texts: Problem running SSIS package remotely using PoSH

texts:
For various reasons I'm trying to run an SSIS package remotely using Powershell.

I've enabled remote scripts on my server using the following command.

Set-PSSessionConfiguration -Name Microsoft.PowerShell -ShowSecurityDescriptorUI -Force

Next I'm trying to run the following script to remotely run DTExec on the server ServerName as the user domain\UserId.

$secpasswd = ConvertTo-SecureString "Password" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ("domain\UserId", $secpasswd)

invoke-command -ComputerName ServerName -Credential $mycreds -ScriptBlock { & `
    'C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTEXEC.exe' `
    -FILE '\\UNC\SSIS_Package.dtsx' `
    -MAXCONCURRENT -1 -CHECKPOINTING OFF  -REPORTING EW }

I've confirmed that domain\UserId has read\execute access to \\UNC\SSIS_Package.dtsx but I am still getting the error:

Microsoft (R) SQL Server Execute Package Utility
Version 11.0.3349.0 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Started:  4:40:34 PM
Error: 2015-04-22 16:40:34.24
   Code: 0xC0011007
   Source: {DAA42715-039F-4355-8121-6910272B5B07}
   Description: Unable to load the package as XML because of package does not have a valid XML f
ormat. A specific XML parser error will be posted.
End Error
Error: 2015-04-22 16:40:34.24
   Code: 0xC0011002
   Source: {DAA42715-039F-4355-8121-6910272B5B07}
   Description: Failed to open package file "\\UNC\SSIS_Package.dtsx"
due to error 0x80070005 "Access is denied.".  This occur
s when loading a package and the file cannot be opened or loaded correctly into the XML document
. This can be the result of specifying an incorrect file name when calling LoadPackage or the sp
ecified XML file has an incorrect format.
End Error
Could not load package "\\UNC\SSIS_Package.dtsx"
because of error 0xC0011002.
Description: Failed to open package file "\\UNC\SSIS_Package.dtsx"
due to error 0x80070005 "Access is denied.".  This occurs w
hen loading a package and the file cannot be opened or loaded correctly into the XML document. T
his can be the result of specifying an incorrect file name when calling LoadPackage or the speci
fied XML file has an incorrect format.
Source: {DAA42715-039F-4355-8121-6910272B5B07}
Started:  4:40:34 PM
Finished: 4:40:34 PM
Elapsed:  0.031 seconds

I don't know if it matters but the the package is from SQL Server 2008 and DTExec is 2012.

Edit:
I've made some headway on this.  It appears to be a double hop problem.  I was able to fix this on one server by using CredSSP.  I've run the following command on my client as an administrator.

Enable-WSManCredSSP -Role client -DelegateComputer * -Force

And this command on each of the servers I'm trying to use.

Enable-WSManCredSSP -Role server -Force

I'm using the following code to test the double hop.  I'm removing the call to DTEXEC to remove that as an issue and just doing a DIR.  I also have to add a CredSSP option to the invoke-command.

$secpasswd = ConvertTo-SecureString "Password" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ("domain\UserId", $secpasswd)

invoke-command -ComputerName ServerName -Credential $mycreds -ScriptBlock { & `
    'DIR' '\\UNC\SSIS_Package.dtsx' }
-Authentication CredSSP

Unfortunately this is only working on one of the 4 servers I've tried it on.  They are all PowerShell 3.  This is the error I'm getting currently.

Cannot find path '\\UNC\SSIS_Package.dtsx' because
it does not exist.
    + CategoryInfo          : ObjectNotFound: (\\ServerName\...kage.dtsx:String) [Get-C
   hildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand
    + PSComputerName        : CRDBLPSQLT02

Any suggestions where I can look next would be helpful.


texts: Is it possible to improve the execution performance of a query with 'simple' joins?

texts:
The query is the following select statement:

select tbl1."DT_REF_TRANS", tbl1."VL_MTM",
       tbl1."VL_FUTURO", tbl1."DT_VENCIMENTO",
       tbl1."QT_PRAZODIASU", tbl1."ID_ATIVO_PASSIVO",
       tbl1."ID_CARTEIRA", tbl1."VL_MTM_ORIGEM",
       tbl1."ID_LOCAL_REGISTRO", tbl1."CD_NIVEL",
       tbl2."NO_ARQUIVO", tbl3."NO_PRODUTO",
       tbl3."ID_ENTRA_2804", tbl3."ID_ENTRA_VAR",
       tbl4."NO_MOEDA", tbl6."NO_SUBGRUPO",
       tbl5."NO_GRUPO_2804", tbl5."PERDA",
       tbl5."RENOVACAO", tbl5."PRZRENOV",
       tbl5."ANTECIPADU",
       tbl5."PRZANTECDU", tbl5."ANTECIPA",
       tbl5."DEPOSMAR", tbl5."RESERVA",
       tbl5."PERCATRASO",
       tbl5."PERCMORAM",
       tbl5."CALCATRASO", tbl5."NORMAL",
       tbl5."CODREGRA", tbl5."CODPRZAJU",
       tbl5."CD_NOVOMTM",
       tbl5."CD_DEPMARG",
       tbl5."ID_CONSOLIDA",
       tbl5."ID_TIPO_GRUPO",
       tbl7."NO_USUARIO_SIST",
       tbl8."NO_GRUPO_2804" as "GRUPOS_2804_NO_GRUPO_2804",
       tbl9."NO_GRUPO", "USUARIOS"."NO_USUARIO"
  from ((((((((("dbo".tbl1 tbl1
  left
  join "dbo".tbl2 tbl2
       on (tbl2."CD_ARQUIVO" = tbl1."CD_ARQ_ORIGEM"))
  left
  join "dbo".tbl3 tbl3
       on (tbl3."CD_PRODUTO" = tbl1."CD_PRODUTO"))
  left
  join "dbo".tbl4 tbl4
       on (tbl4."CD_MOEDA" = tbl1."CD_MOEDA"))
  left
  join "dbo".tbl6 tbl6
       on (tbl6."CD_SUBGRUPO" = tbl3."CD_SUBGRUPO"))
  left
  join "dbo".tbl5 tbl5
       on (tbl5."CD_GRUPO_2804" = tbl3."CD_GRUPO_2804"))
  left
  join "dbo".tbl7 tbl7
       on (tbl7."CD_USUARIO_SIST" = tbl5."CD_USUARIO_SIST"))
  left
  join "dbo".tbl8 tbl8
       on (tbl8."CD_GRUPO_2804" = tbl3."CD_GRUPO_2804"))
  inner join "dbo".tbl9 tbl9
       on (tbl9."CD_GRUPO" = tbl3."CD_GRUPO"))
  inner join "dbo"."USUARIOS" "USUARIOS"
       on ("USUARIOS"."CD_USUARIO" = tbl1."CD_NIVEL"))

You can see the execution plan here.

I've created and dropped indexes in columns inside the joins, have been testing with some hints ( like left loop join for example ( it helped me a lot in the past )). but, is there a way to improve a query that's not using where, or nothing else?

It's running in 30Seconds, returning 1M rows+.

Should I use IN somewhere? Exists? Some hint I don't know?

See, the question is not "hot to update THIS query". I would like to know tips to improve queries using this structure in general.


texts:
For example, I have two tables: Question(QuestionID, QuestionTitle, QuestionContent) and Answer(AnswerID, QuestionID, AnswerContent).

Now, I have a Vote table that support vote for both Question and Answer. I have three ways for designing database:

1. Vote(VoteID, QuestionID, AnswerID) // always QuestionID or AnswerID is null

2. Vote(VoteID, PostID, type) // type = 0. PostID is Question. type = 1. PostID is Answer

3. VoteAnswer(VoteID, AnswerID)
   VoteQuestion(VoteID, QuestionID)

I don't know which database design I should follow in those cases, using ORM and just using native query.

Thanks :)


texts: Can Oracle row-level security work with user session credentials?

texts:
Can row-level security in Oracle be controlled by a user's session credentials or user context? Here's an example to illustrate what I'm looking for:

Our database contains a bunch of sensitive company information, including salaries. HR administrators should be able to see salaries in their search results, but facilities management staff shouldn't, even if they use the same search parameters. The catch is that our app only has a single database user, and all requests go through it, so the security can't be set up to simply check the database user's ID. We'd have to pass the info in as, say, a user context.

We're considering moving to Oracle Access Manager/WebLogic, if it makes a difference.


texts: How to remotely manage (with PhpMyAdmin) a MySQL RDS on a load-balanced AWS Elastic Beanstalk setup?

texts:
Although there are similar questions and topics, I can't find a solution that currently works.

I specifically wrote remotely manage because I'd like to use my home computer's PhpMyAdmin to directly connect to the RDS instance and keep its 'public accessibility' to private.

What I've done so far:

installed PhpMyAdmin on my home computer (mac osx) and got it working
added an inbound rule for the security group used by the RDS (rule: my.home.network's.ipv4/32, port 3306)
configured my mac's phpmyadmin conf.inc.php according to this suggestion. I've never done SSH tunneling, but the idea seems safer than opening 3306 to the public. So I've set it up accordingly, but these are the PhpMyAdmin errors I get:

Cannot log in to the MySQL server
mysqli_real_connect(): MySQL server has gone away
mysqli_real_connect(): Error while reading greeting packet. PID=xxxx
mysqli_real_connect(): (HY000/2006): MySQL server has gone away

Any ideas what I'm missing/doing wrong? Also, do I need to have MySQL at all installed on the mac? I assumed I didn't need it since I'd be using the remote one, but I've installed it anyway, and tried both with and without it. Same errors.

Thank you!


texts: Windows patching on 3 node Always On Availability Group

texts:
We have a three node Always On Availability Group cluster. We normally patch the passive nodes (N2, N3), fail over from N1 to N2, patch N1, and then fail back to N1. N1 and N2 are sync, N3 is async.

I am in a argument with our colleagues on weather to fail back to node 1 after patching.  My colleagues say we have to do this, so that we make sure Node 1 is functional.  I am of the opinion that we keep SQL Server on N2 so that we avoid another interruption of failing over to N1.

I was wondering if the practice of failing back to N1 to make sure the node is healthy is warranted or just useless?


texts: What is causing BETWEEN to be replaced with =?

texts:
We have a Visual Studio solution for our SQL Server 2019 database, and it is stored in a git repo. One table has a couple of check constraints using the BETWEEN operator, like this.
CONSTRAINT chk_FileTbl_MonthlyDate CHECK (MonthlyDate BETWEEN 1 AND 31)

When I used Redgate SQL Compare 14 to compare the repo to the database, it displayed the constraint reformatted with less than and greater than operators, like this.
CONSTRAINT chk_FileTbl_MonthlyDate CHECK (MonthlyDate >= 1 AND MonthlyDate <= 31)

I thought it was a bug/feature in Redgate's software and submitted a ticket for which I am awaiting a response. However, my colleague then used the comparison tool in Visual Studio 2019 and it did the same thing. If we look at the file defining the table, it uses BETWEEN, but both comparison tools show and deploy a change script with the less than/greater than format.
What is causing this behavior? We somewhat recently moved to SQL Server 2019, is it some kind of built-in optimization?


texts: Ola Hallengren Transaction Log Backups of System Databases

texts:
I am attempting to backup the transaction logs of master, model in addition to the msdb.

MSDB transaction log backs up without any issues, but the master and model do not.

Here is the script that I have included in a new Agent job (copied from DatabaseBackup - System_Database - Full) which I have named DatabaseBackup - System_Database - Log.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'SYSTEM_DATABASES', @Directory = N'\\synologymb\sql backups' , @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y', @LogToTable = 'Y'" -b


texts: Finding two most recent payments for every sale

texts:
Trying to get the two most recent payments for each sale number from a table of transactions (ARTRANS). Seems like a lateral join would work or maybe a window function.

Structure is fairly simple with ARTRANSID (the PK), SALENO, PAIDDATE, DUEDATE, ACCTNO (more, really, but what does that matter). Of course, with the lateral join, I'd really be doing a self join. Any suggestions on this code?

An aside: It would be cool if there were something like a GROUPLIMIT keyword, so you could limit the returned records to a certain number within the grouping. That would make this easy as pie.


texts: Why do subsequent unmet conditions in my CASE statement affect my return data type?

texts:
I have the following variables and CASE statement:
DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'

SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/2021', @DATE_VALUE)
       END AS ConvertedValue

There's nothing special about it. It returns 8 (int) as I expect it to.
However, as soon as I add to my CASE statement a conditional that returns a datetime data type, I no longer get 8 (int), despite continuing to use "I" as the @CONVERT_CODE value.
DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'

SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/2021', @DATE_VALUE)
                          WHEN 'D' THEN GETDATE()
       END AS ConvertedValue

Instead, I get the datetime value of "1900-01-09 00:00:00.000"
I would think "D" shouldn't get evaluated at all because, in SQL Server, once a condition evaluates to true ("I" in this case), it should break out of the CASE statement--so I'm not sure why "D"s data type is affecting the overall return data type. Also, it doesn't matter if I reverse the order of the conditions and still use "I":
DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'

SELECT CASE @CONVERT_CODE WHEN 'D' THEN GETDATE()
                          WHEN 'I' THEN DATEDIFF(day, '04/21/2021', @DATE_VALUE)
       END AS ConvertedValue

Am I missing something?


texts: Clarification on Cloud Computing Database

texts:
This question can be very generic, but I am trying to understand the concept of Cloud Databases.

Let's say I have a Web Application, a Desktop Application, and Apps for my Web Application.

All of my Apps, Web, and Desktop applications communicate with the Database when it is online.

My question is, if there is a situation to work offline, can I work offline on the Desktop App and later sync with Cloud Database?

Just like Dropbox, we can add files to the Dropbox in an offline mode.When Dropbox goes online, Folder will get sync automatically and update all the systems like iPhone - App or other Computer Dropbox folders.


texts: Availability Group Clustering - Cluster Group Preferred Owner

texts:
We have a three node AG. Two nodes in a local data center, one in a different subnet DR site.  Am I correct that the Cluster Name should have two IPs, just like the listener?

Also, is there a way to set the preferred owners of the Cluster Name and Witness so it won't try to fail over to the DR site if the primary nodes are up?

Additional Info: We had an outage from patching a secondary node, we couldn't figure out why a secondary rebooting caused the AG & Cluster to go down.  We opened a support ticket with Microsoft and they recommended we change the possible owners of the Cluster Group to exclude DR.  So what appears to have happened is the Cluster Group tried to fail over to the DR site, it couldn't come online because there was no subnet IP. That took just long enough that quorum was lost because the witness didn't come online, and the secondary was rebooting.


texts: How to keep long multiple of questions and answers in database?

texts:
I am planning my database design to have 3 sets of multiple questions for 3 types of different customers (at least 3 at the moment), each set of them contains approx. 100 questions and waiting for their answers to be kept in the database.

These questions are in a required questionnaire that is a very crucial material for my job, then posting them all as an email to myself is not an alternative. I am no expert here in this field. I am not sure what is the best approach to store, retrieve, and manage them.

Please take a look at my database design and advice for a better approach.

TABLE: QUESTIONS

SETID
Q1
Q2
Q3
...

TABLE: QUESTIONNAIRE

CUSTOMERID
SETID
A1
A2
A3
...

I have got 3 main problems:

How to display each set of Q&A on PHP page based on customer profile?
The questions need to be displayed on the web page, if by manually, text display, that would have been a time-consuming job for me if I decide to remove, edit, or delete some questions. What way would you recommend?
All the questions (Q1,Q2, and ...) in QUESTIONS table need to map exactly as the answers (A1,A2,and...) in QUESTIONNAIRE table, is there any better approach?


texts: How to dump only the MySQL database structure?

texts:
I am trying to take dump of MySQL database structure using the following commands:

mysqldump -d -h localhost -u root -p databasename > dumpfile.sql
mysqldump -d -h localhost -u root -p database databasename > dumpfile.sql
mysqldump -d -h localhost -u root -P 3306 -p databasename > c:\dumpfile.sql

What is wrong with them? Where should I get this file?


texts: MariaDB 5.5 -- how to customize password rules

texts:
I am using RHEL 7, which includes MariaDB 5.5.

I want to set up minimum requirements for passwords: minimums length 15, 1 lowercase, 1 uppercase, 1 symbol, 1 number.

I figure that I am probably not the first person using Rhel 7 to face this problem.  Does anyone have some guidance on the best approach to add the above password rules to MariaDB 5.5 under Rhel 7?

I have so far pieced together these facts.  (Some I am not so sure are correct.)  I believe these facts indicate my best option is to just do whatever 5.5. allows.

MariaDB 5.5 doesn't do this. There is no plugin.
MariaDB 5.7
introduced new password features.
MariaDB 10.1 and later has password
plugin with this functionality.
Installing an updated MariaDB on Rhel
7 would requiring making my own RPM and possibly put the system
security at risk.
I could write my own password routines and put them
in the database.  But this is not recommended, because it is easy to
introduce a security flaw.
I can configure 5.5 to work with Linux's
PAM, or use the standard linux-user-name-matches-the-mysql-account-name authentication.


texts:
I would like to select from a function that return a  SYS_REFCURSOR Type value.

for example :

CREATE OR REPLACE FUNCTION my_funtion (
        my_param IN VARCHAR2)
     RETURN SYS_REFCURSOR
  IS
     l_return   SYS_REFCURSOR;
  BEGIN
           OPEN l_return FOR
                SELECT last_name, first_name
                  FROM employees
                  WHERE id = my_param
              ORDER BY employee_id;
     RETURN l_return;
END my_funtion;

I would like to do something similar to this :

select * from  my_function('id015');

or even :

select alias.last_name from  my_function('id015') alias;


texts: How can I make UPDATE to return an error if no rows are affected in Postgresql?

texts:
In my application code, I have an ordinary UPDATE statement to update some row's properties. However, I've encountered a bug where this statement was provided with an incorrect ID and no row was affected.
This bug was silent because UPDATE statement that affects no rows still finishes successfully. I want to prevent such bugs in the future, and to do so, I want to instantly and loudly fail if this happens. One option that I have is to include a RETURNING statement and check it in the application code, but before going with it (and updating the whole application codebase where this pattern occurs — which is a massive change), I wanted to check the alternatives.
Is there a way to write an UPDATE statement in such a way that it produces an error if now rows are affected?


texts: How unsafe is it to change the data source of repl_distributor on a publisher

texts:
Here's my situation.

Reporting Server (SQL 2008) is distributor/subscriber cluster for a SQL 2000 Transactional Publication
Reporting Server was migrated to new SAN
Reporting Server fatally crashed upon completion of migration (model db was corrupted)
I installed another instance of SQL on a node of the cluster, attached dbs, and changed report connections to use this new instance
Stupidly, I attached the [distribution] database to this new instance
select * from sysservers (rightly) returns that the distributor is still the first instance on the cluster.

How terribly would it break things if I ran the following on the publisher (or is this even possible)?

sp_configure ‘allow updates’, 1
 GO
 RECONFIGURE WITH OVERRIDE
 GO

update sysservers set datasource = 'server\instance' where srvname = 'repl_distributor';
GO

sp_configure ‘allow updates’, 0
 GO
 RECONFIGURE WITH OVERRIDE
 GO

Is this even the best way, or is there an alternative way to change the distributor?


texts: Restrict update on certain columns. Only allow stored procedure to update those columns

texts:
I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not using the stored procedures designed to update it.

I am considering implementing this using triggers and a token table. The idea I am considering is to have a token table. the stored procedures will have to first insert values in the token table. Then update the price columns. The update trigger will check if the token exists in the token table for the updated row. If found, it would continue. if the token is not found, it will throw an exception and make the update transaction fail.

Is there a good/better way to implement this restriction?


texts:
I am using a job to update a materialized view.

I found that the job would lock itself if the exec dbms_refresh.refresh(''"<schema>"."<materialized_view_name>"''); was not committed and I would like to change the "what" column of the job to include a commit.

I face two possible solutions :

solution 1: a more complicated way is to recreate the job entirely, which I would like to avoid

Run SYS.DBMS_JOB.REMOVE(14133); COMMIT;

Recreate the whole job to now include the commit

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'exec dbms_refresh.refresh(''"<schema>"."<materialized_view_name>"'');COMMIT;'
   ,next_date => to_date('11-11-2016 00:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'trunc(sysdate)+1   '
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

solution 2: update the DBA_JOBS view directly by running a normal update statement on the view

update dba_jobs set what=(
select what from dba_jobs
where job in ( '1','2','3','<etc>')
)||'COMMIT;'
where job in ( '1','2','3','<etc>');

Is there a better solution than these two, is solution two possible ?

I'd like to avoid solution 1 as let's imply I don't have the code for the view at hand it would take more time plus let's imply I have hundreds to modify.


texts: JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)

texts:
I have a database which stores receiver to indicate which account the data relates to. This has led to tons of duplication of data, as one set of data may create 3 separate rows, where all column data is the same with the exception of the receiver column. While redesigning the database, I have considered using an array with a GIN index instead of the current B-Tree index on receiver.

Current table definition:

CREATE TABLE public.actions (
    global_sequence bigint NOT NULL DEFAULT nextval('actions_global_sequence_seq'::regclass),
    time timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    receiver text NOT NULL,
    tx_id text NOT NULL,
    block_num integer NOT NULL,
    contract text NOT NULL,
    action text NOT NULL,
    data jsonb NOT NULL
);

Indexes:

"actions_pkey" PRIMARY KEY, btree (global_sequence, time)
"actions_time_idx" btree (time DESC)
"receiver_idx" btree (receiver)

Field details:

Global sequence is a serially incrementing ID
Block number and time are not unique, but also incrementing
Global sequence and time are primary key, as the data is internally partitioned by time

There are some receivers that have over 1 billion associated actions (each with a unique global_sequence).

Average text lengths:

Receiver: 12
tx_id: 52
contract: 12
action: 6
data: small-medium sized JSONB with action metadata

Cardinality of 3 schema options:

Current: sitting at 4.2 billion rows in this table
Receiver as array: Would be at approximately 1.8 billion rows
Normalized: There would be 3 tables:

Actions: 1.8 billion rows
Actions_Accounts: 4.2 billion rows
Accounts: 500 000 rows

Common Query:

SELECT * FROM actions WHERE receiver = 'Alpha' ORDER BY time DESC LIMIT 100

All columns are required in the query. NULL values are not seen. I believe joins in the normalized schema would slow down & query speed is #1 priority)


texts: How to determine queries per hour executed by a particular MySQL user?

texts:
I am trying to implement user based quota system in MySQL to limit MAX_QUERIES_PER_HOUR  for a particular user. I have an option to set this limit via MAX_UPDATES_PER_HOUR 10.
But my problem is that I want know that how many queries a user has executed this hour.  I followed this thread but this is for global statistics of dba.  I want this information for every user of my database. I am sure that MySQL is storing this schema somewhere but after a lot of scrutinizing some of databases like information_schema , performance_schema , mysql I can't figure out how it will be achieved.


texts: Why should I create an ID column when I can use others as key fields?

texts:
Possible Duplicate:
  Why use an int as a lookup table's primary key?

So far, I'm accustomed to creating an ID column for every table and it is practical in a way that it makes me not think about decision making about primary key theories.

The professor in my university suggested the class to make primary keys from one or more fields which make up to one unique info about each column. And yes, I want to have a habit of applying natural keys instead of surrogate keys. On Wikipedia, the advantages and disadvantages of surrogate keys are listed, I strictly recommend This Article

I have seen people use integer ID fields for everything and nobody judges this method because

it "looks" efficient
a number field is used and it looks cooler because of its size per
row in memory

I am beginning to think an extra ID field is just creating redundant data with no actual benefit. So why should I create an ID column when I can use other columns as key fields?

If your ID field is 32 bits, it's equivalent to 4 ASCII characters
already.
If your Id field is 64 bit integer, it's 8 characters string so it
doesn't actually save that much of  memory (what implied here is the memory used on comparison. extra id column is already adding to the memory used (both HDD and RAM) )
An extra ID field doubles your indexing cost because you will also
    index a unique field you can use as a primary key.
You make extra joins if you need the data you could have used as a key field, for example, if you stored a unique user ID in one
blog post, to show the name of the author, you make a join query, if
your key field was the author's name, you don't need to join because
you store the relevant data in the blog post table. a foreign key
field with meaningful data reduces the need for subquery or join

Creating an extra id field "adds" to the memory load, it's not a
replacement of a unique string field, you are not replacing a
char-varchar field with an integer, you are adding an extra
column and it creates extra data flow. so any comparison of
data store should be done between "string" and "int+string". adding
an integer id field does not save space.

on the other hand

assigning a primary key data which gets value from user input, can be
problematic because people can enter, for say, their social security
number wrong and the actual person who wants to register won't be
able to register because of the unique policy. This can be
circumvented by adding extra digit or digits to the original number.

Extra resources:

Comparison of Natural vc Surrogate keys

My conclusion from reading articles is that I should use natural keys whenever possible instead of skipping thinking about natural keys and using surrogate keys each time, as if it is a standard.


texts: Whenever I tried to import file in MySql on my Linux Mint system, "You probably tried to upload a file that is too large. Please refer to documentation for a workaround for this limit." this message shows. May anybody please help me that how to import 1.5GB. Thanks


texts: Does it make sense to create a new database for every user? (MySQL)

texts:
I want to make a website which will have approximately 10M+ users. My MySQL database will be huge! Because the users will chat, post, share etc.

I will use dreamhost. They can provide me unlimted databases but they told me to keep the database size below 2-3GB. I'm afraid if I use a single database it will exceed 2-3GB in a week or two.

So the question is - should I create a new database per user?


texts: String parsing: Extracting the filename from a URL

texts:
This is a link I have in a column of a table:

http://earthexplorer.usgs.gov/browse/etm/1/11/2000/LE70010112000130KIS00.jpg

There are like 1000's of rows like this.

Is it possible to extract the FileName like LE70010112000130KIS00 (without the path and filename extension) and put it in another column?

I am new to SQL Server.


texts:
Is there way we can get the table size and DB size growth from last one month day-wise?

We are able to give the current size of DB and table size.


texts:
SELECT
SUM(coins.available_supply * hist_all.price) AS market_cap_usd_all
FROM coins
    LEFT JOIN hist_all ON coins.id = hist_all.coins_id
    WHERE hist_all.price != 0
AND (hist_all.second_coin = 'USD' or hist_all.second_coin = 'USDT');

I try get sum market_cap_usd_all, but in my table are rows with USD and USDT:

My query should like this:

if exists row second_coin = USD, DON'T GET this same row with 'USDT', but if don't exists row with second_coin = USD check if it exists row with second_coin = USDT and get him. So the table should like this:

I don't know how write query with WHERE IF I tried this:

... WHERE IF (hist_all.second_coin = 'USD', hist_all.second_coin = 'USD', hist_all.second_coin = 'USDT')

but it still sums all rows, both from USD and USDT.


texts: Is there a way to see what locks a stored procedure obtains?

texts:
I would like to run a stored procedure, and obtain an analysis of all the locks that it took while running. Is there a way to do that? If not automatically, then is there a resource that will explain to me how to figure it out statically?
I'm running Postgresql 9.4 but am open to solutions that only work in newer versions since we're planning to upgrade.


texts: Selecting 'edge' records in a sequence of data


texts:
I am planning on taking our current single 2014 SQL server and rebuilding it to a 2019 SQL Server HA AlwaysOn Cluster. The issue is, the listener IP and hostname has to stay the same and many years ago, someone decided to create a fileshare on it and use it for a couple reports and development processes.

In my tests, you can't access an SMB file share on a SQL listener. All of my research just talks about witness file shares and I can't find anything for an actual SMB user file share. I would rather just get rid of it, but I don't think there is a reasonable way to remove this file share.

Is there any kind of trickery with SQL or WSFC that I can provide access to this fileshare on the same IP/Hostname as the SQL listener?


texts:
I have a table like this:

And I want to get something like:

fecha       siguiendo    seguidores
2016-01-02    430           1430
2016-01-03    450            NULL
2016-01-04    NULL           5000

I have two types of data seguidores and siguiendo. I want a column for each of them.

I have tried to emulate a FULL JOIN on the same table but I didn't get the expected result. Any ideas?


texts: Query returns results on pgAdmin running on remote client but not directly through psql on db server

texts:
When I query my database using a role in pgAdmin on a remote client, I get the results of the query. However if I run the same query directly on the db server with the same role , I don't see the query output. I see the table are present with the "\dt" command but I am not able to view the results of the queries on these tables. Is there some configuration that is needed to enable querying the db from the same host ?

Note:
Made edits to the question based on the comments.


texts: What are the drawbacks of using DOMAINS to store metadata about the column?

texts:
I came across a question about a data type for MD5 values. The recommendation in that question states using uuid as type for that field.

The argumentation is solid. But I find that this might be confusing to someone unaware of the points revealed in the above question. The decision to use uuid as MD5 type is something that I would like to see in some form of documentation in any project that does this.

As a "helper" to whoever looks into the DB Schema, one could argue to create a md5 domain inheriting from uuid. That way, the column types would be much more explicit and the intention would be much clearer.

But it would use a domain for no other reason than to "rename" an existing type.

It's still something that would make sense to document properly. But that documentation could be centralised to a section explaining the domains in the DB. So you wouldn't gain anything in terms of documentation. The advantage I see is, as mentioned, that the intention becomes clear when looking at the table DDL.

Are there any downsides to this?


texts: Change Root Directory on server properties page in SQL Server 2014 SSMS

texts:
We did in inplace upgrade from SQL 2012 to SQL 2014 (I know it wasn't the best choice, and it won't happen again, so no need to beat that horse). I have both the MSSQL11.MSSQLSERVER and MSSQL12.MSSQLSERVER Folders. The database operates correctly, but the data is located in the MSSQL11 folder which isn't a problem for us, but... in the Server Management Studio under server properties the 'root directory' field on the general page points to the MSSQL12 folder, and our automated backup system (which is part of a larger software suite) uses that field to place the backups in the MSSQL12 folder.

Question:

I'd like to change the 'root directory' to point to MSSQL11 and have
the data and backups in one place.
Does anyone know what that field is used for (besides the obvious
backup system)?
Is it relatively safe to change it?

I would of course take a snapshot of the VM before changing and test it before I went live, but I wanted to avoid problems if possible.

Thanks!


texts: Install Oracle Database 12c Error unable to check whether the location specified is on CFS

texts:
ps://i.stack.imgur.com/0DX0d.png

I am trying to install the Oracle database 12c on the Windows 10 Laptop.

Have tried disk C and disk E, got the same error.

And I tried to change the file properties, change the security to allow the full control.. And still got the same error message.

Anyone could help me with this? Thank you!!!!


texts: Where do i change the my.cnf on mysql cluster

texts:
I'm trying to change the max_allowed_packet setting on mysql cluster (version 7.4.8) - i have 5 nodes

2x SQL Node
2x Data Node
1x Managament Node

I have updated the my.cnf on all of the servers ->

[mysqld]
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
user=mysql
ndbcluster
ndb-connectstring=manager-db
default_storage_engine=ndbcluster
port=3306
max_allowed_packet=1073741824

But show variables like '%max_allowed_packet%' still returns 524288000

What am i doing wrong ? where do i need to set it ?


texts: What are the pros/cons of between these 2 database design options where the join table can have 2 or 3 foreign keys?

texts:
I am trying to model the following scenario, but I have come up with 2 options and I cannot figure out how to decide between the 2 options.  Both options seem to work, but I worry that I am missing some edge case that suggests one over the other.  Any recommendations or suggestions on where one option might be better than the other option?
Thank you for your time :)
The model:
A user has many labels
A property has many labels
A label has many properties

Option 1 (join table with 2 foreign keys):
user
----
id
name

property
--------
id
name

label
-----
id
name
user_id (foreign key for user.id)

property_label
--------------
property_id (foreign key for property.id)
label_id (foreign key for label.id)

Option 2 (join table with 3 foreign keys):
user
----
id
name

property
--------
id
name

label
-----
id
name

user_property_label
-------------------
property_id (foreign key for property.id)
label_id (foreign key for label.id)
user_id (foreign key for user.id)

Potential Queries:
Get all labels associated with a user
Get all properties associated with a label of a user

Example data:
User1 has labels "old" and "new" for property "bicycle".
User2 has labels "1 year", "2 years", and "3+ years" for property "bicycle".
User 3 has labels "<3 years" and "3+ years" for property "car"
User 4 has labels "red", "blue", and "black" for property "car"
User 5 has labels "daily beater" and "weekend fun" for property "car" and labels "<3 years" and "3+ years" for property "bicycle"
User 6 has labels "1 year", "2 years", and "3+ years" for properties "bicycle" and "car"

The list of properties is large, but finite.  Users will reference the same property and cannot have their own variation in its spelling.  So for example, "bicycle" will always be "bicycle" for every user.  A user cannot have "bike" or "bi-cycle" or some other spelling of the property.
The labels are all user defined so it can be infinite depending on how each individual user wants to label their properties.
Update:
While musing on Jim's comments, the solution came.
The join table with 2 foreign keys is better because it is further normalizing the data.
For instance, this scenario:
User1 has labels "old" and "new" for property "bicycle"
User1 has labels "old" for property "car"

will produce this data using the option with 3 foreign keys:
user (id, name)
---------------
1, User1

property (id, name)
-------------------
10, bicycle
11, car

label (id, name)
----------------
100, old
101, new

user_property_label (user_id, property_id, label_id)
----------------------------------------------------
1, 10, 100
1, 10, 101
1, 11, 100

The user_id foreign key is redundant information because user_id:1 will always be paired to label_id:100.  Hence, the data can be further normalized by using the 2 foreign key option. And will look like this instead:
user (id, name)
---------------
1, User1

property (id, name)
-------------------
10, bicycle
11, car

label (id, name, user_id)
-------------------------
100, old, 1
101, new, 1

property_label (property_id, label_id)
--------------------------------------
10, 100
10, 101
11, 100


texts: How to determine why a MySQL server has become slow and requests sometimes time out?

texts:
I'm working on my first project that makes heavy use of a MySQL database and am struggling to understand performance issues I'm having.

Most of the time, responses are returned quickly from the server, but when I have certain scripts that make a lot of queries (both reading and writing data) running in the background, I'm finding that other requests are slow to receive responses, or are timing out entirely.

As an example, yesterday when a PHP script that makes a lot database queries was running, requests to the server for pages that made a number of queries before returning a response were timing out -- I couldn't even connect to the database with Navicat, as it timed out as well.

In the above example, I ran top/htop in an SSH console and found that there was very low CPU usage and memory usage was only at about 50%. Because of this, I think the problem lies with the database, but I'm having trouble understanding what is causing the issues, such as whether they might be due to:

Slow queries
Too many connections
Too many queries per second
Other potential issues I'm not aware of

I know about the slow query log, but none of my queries are very slow.

What methods or tools can I use to determine what is causing a MySQL database to become slow, sometimes with requests even timing out?


texts:
Folks,

I am running MySQL on Windows8 for my personal (casual) use. I am trying to determine if there is a way to automatically and cleanly shutdown MySQL whenever I shutdown my Windows8 computer. Ideally, Windows is supposed to automatically stop the MySQL service as part of OS shutdown sequence. But I am not sure if it actually happens though. Any thoughts/ideas/recommendations around this subject will be appreciated!

Additional Information: Currently I am using a .bat script to do a "net stop" on the mysql Windows service. I manually invoke this .bat script just before I shutdown my computer.

Thank You.


texts: Should I create a column for specific behavior?

texts:
I would like to expose a problem I often have when I work with database and I never knows what to do.

I need to load data from a file into a table (table1) line by line with the help of a software. The file can be a csv or something similar. During the loading I also check the validity each line and each value in this line. If something is wrong I put a message another table (table2) with a reference to my line. A line can have more than one error.

So in my table1 I can have valid and invalid line. To check if a line is valid or not I need to check if there is a message, a line, in my table2. I hope this is clear.

My question now. Thinking about database design should I create a column valid (true/false) in my table one to avoid the have to check table2 each time I want to get value from table one.

Example:

table1:
reference | version | var1 | var2 | var3
----------------------------------------
 001      | 1       | x    | y    | z
 001      | 1       | a    | b    | c
 001      | 2       | null | y    | z
 001      | 2       | null | t    | u
 001      | 3       | x    | y    | z
 001      | 3       | a    | b    | c
 001      | 4       | null | y    | null
 001      | 4       | null | t    | null

table2:
reference | version | message
------------------------------------------
 001      | 2       | var1 cannot be null
 001      | 4       | var1 cannot be null
 001      | 4       | var3 cannot be null

In table1 I have an item 001 with 4 versions. Version 4 is not valid. This information of validity come from table2.

I always need to be able to request the latest valid reference. Get the last valid reference. To do so I need the check into 2 tables.

Check last line of table1 then compare it with table2. If invalid ignore last invalid line then repeat the request. This seems so heavy...


texts:
Will preface this by saying I am not at all convinced yet this is not the vendor doing something wrong on their application server.  Just looking for new ideas

Instance X is a named instance of SQL Server 2008 R2.  It houses numerous databases and takes in connections from numerous application servers.  Browser service is running and all connections are made by way of server\instance name.

Moved DB from an old 2K5 instance over to it the other day and vendor claims he cannot connect to the DB.  He says he is just putting in the Server\instance name in his connection string and his connection fails. At this time he cannot/has not provided me with any real error message.

Whenever he tries, we see no evidence of success or failure.  As of right now there is no evidence he is even hitting the named instance.  He can hit a test 2008 R2 default instance though.  So, since the named instance appears to be the difference, I am thinking this has something to do with the port number.  We have not explicitly tried the port number because we need it to work with just the name and vendor swears it should.  However, we can connect with his credentials, via SQLCMD and by setting up a basic ODBC.

Application server is 2003R2 and in my opinion, craped up, but replacing it is not an option.  DBNETLIB.DLL version is 2000.86.3959.0 and native client version is 2005.90.3042.  You would have to go quite a ways back to not be able to hit a named instance.

Am I missing anything overly obvious?  From a customer service perspective would prefer to not just say “Works for everyone else!”.

EDIT 9:44PM Central - Should have noted on original post that the firewall is not on on the application server and we do not filter/block anything internal to the network.


texts: Where can I find the connection string for a DB2 database?

texts:
I need to connect an application to my DB2 database. Both application and db are running on the same machine.

I've tried this:

Server=localhost:50000;Database=myDataBase;UID=myUsername;PWD=myPassword;

I'm getting the following error:

  CLI0124E  Invalid argument value. SQLSTATE=HY009

Am I using the wrong connection string?

Edit
I've also tried the following format:

Provider=IBMDADB2;Database=urDataBase;Hostname=urServerAddress;Protocol=TCPIP;Port=50000;
Uid=urUsername;Pwd=urPassword;

Same result.


texts:
I have a text column containing various words that have been shortened into abbreviations.  For example, instead of "insured", the column might contain "insd".  I understand I can use a thesaurus file to create a list of synonyms, effectively allowing a search for "insd" to return rows that contain both "insured" and "insd".  Perfect.

However, some of the other abbreviations in the column contain "special" characters, like a slash or an ampersand, such as:

t/p - third party
o/s - other side
p/p - per person
i&o - inside and out

Is there some way I can make CONTAINS or FREETEXT understand queries containing these words?  For instance:

SELECT *
FROM dbo.MyTable
WHERE FREETEXT(MyColumn, 't/p');

I've created the thesaurus file, tsenu.xml in the correct location for the instance:

<XML ID="Microsoft Search Thesaurus">
    <thesaurus xmlns="x-schema:tsSchema.xml">
    <diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            insured
            insd
        </expansion>
        <expansion>
            t/p
            third party
        </expansion>
        <expansion>
            o/s
            other side
        </expansion>
        <expansion>
            p/p
            per person
        </expansion>
    </thesaurus>
</XML>

And loaded it using EXEC sys.sp_fulltext_load_thesaurus_file 1033;, however the query returns unpredictable results.


texts: How to find if a column is referenced in a computed column?

texts:
I'm trying to mass retype columns. That means first dropping and recreating any constraints they are part of.

I found columns referenced by these constraints

Foreign Keys,
Primary Keys,
Indexes,
Check constraints,
Rules,
Default constraints.

But I cannot find Computed columns.

I've looked into INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE, but it doesn't include Computed Columns.

There is also sys.computed_columns which shows definition, but doesn't list columns in searchable manner.

Is there anywhere else I can look? If SQL Server can figure out the dependence, I thought I would be able to as well.


texts: Do I need a separate IP address per subnet for every database in a Basic Availability Group?

texts:
I have a pair of SQL Servers running in two different subnets (in AWS if that matters) and a couple of databases that I want to run in a Basic Availability Group.

My understanding is that in SQL Server 2016 Standard I need a separate Availability Group for every database. Also at least in the user interface it looks like an AG Listener can only belong to a single Availability Group.

I get errors when I try to reuse the IP address for another AG listener (i.e. use 10.0.0.1 and 10.0.1.1 for AG listeners LISTENER1 and LISTENER2)

Do I really need to create a separate AG Listener with two unique IP addresses for every AG and thus every database, or is there a way to reuse either the IP addresses or the AG listeners?


texts: Log debug messages in a SQL CLR C# Stored Procedure

texts:
I have developed a SQL CLR stored procedure in C#, for SQL Server 2012.

I want to log messages to SQL Server registers. On a normal stored procedure I use xp_logevent

What can I use to log a message in a SQL CLR stored procedure?


texts: Postgres LEFT JOIN with recurring dates and users

texts:
I'm trying to get a query to show me if users have a record on a particular day, and if they don't it would return a NULL.

What I have so far is a I create a sequence of days, then LEFT JOIN it, if I do this on a single user, I get exactly what I need, but if I do the same over multiple users I don't because the table with the records has all users, so it will always be able to join a day at any one point

This is the query I'm running now

WITH days AS (
  select generate_series((current_date - interval '7 days')::timestamp, current_date::timestamp, '1 day'::interval)::date AS day
), eves AS (
    SELECT COUNT(id) as evs, user_id,
        DATE_TRUNC('day', created_at)::timestamp AS full_day
    FROM logged_events
    WHERE DATE_TRUNC('day', logged_events.created_at) > current_date - interval '7 days'
    GROUP BY user_id, full_day
)
SELECT
    eves.full_day, days.day, eves.user_id FROM days
LEFT JOIN eves ON eves.full_day = days.day
    GROUP BY eves.user_id, eves.full_day, days.day

But like I said because on logged_events I have records from all users, when I join days it will always find something to join, if I were to limit to a single user_id in logged_events I would get the output that I am looking for

This is what I am getting

      full_day       |    day     | user_id | evs
---------------------+------------+---------+-----
 2016-05-20 00:00:00 | 2016-05-20 |       4 |  21
 2016-05-21 00:00:00 | 2016-05-21 |       4 |   3
 2016-05-22 00:00:00 | 2016-05-22 |       4 |   5
 2016-05-23 00:00:00 | 2016-05-23 |       4 |  47
 2016-05-24 00:00:00 | 2016-05-24 |       4 |  26
 2016-05-25 00:00:00 | 2016-05-25 |       4 |   2
 2016-05-20 00:00:00 | 2016-05-20 |      19 |  11
 2016-05-24 00:00:00 | 2016-05-24 |      19 |   8

And looking to have

      full_day       |    day     | user_id | evs
---------------------+------------+---------+-----
 2016-05-20 00:00:00 | 2016-05-20 |       4 |  21
 2016-05-21 00:00:00 | 2016-05-21 |       4 |   3
 2016-05-22 00:00:00 | 2016-05-22 |       4 |   5
 2016-05-23 00:00:00 | 2016-05-23 |       4 |  47
 2016-05-24 00:00:00 | 2016-05-24 |       4 |  26
 2016-05-25 00:00:00 | 2016-05-25 |       4 |   2
 2016-05-20 00:00:00 | 2016-05-20 |      19 |  11
 2016-05-21 00:00:00 | 2016-05-21 |      19 |  NULL
 2016-05-22 00:00:00 | 2016-05-22 |      19 |  NULL
 2016-05-23 00:00:00 | 2016-05-23 |      19 |  NULL
 2016-05-24 00:00:00 | 2016-05-24 |      19 |   8
 2016-05-25 00:00:00 | 2016-05-25 |      19 |  NULL


texts: I have a correct subquery that returns results but when I use it as an "IN" clause I get error 1064

texts:
This must be a very simple problem, the subquery returns results alone but the compound query reports:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table where table in (select table_name from information_schema.tables where tab' at line 1

Here's the query:
select *
from table
where table in
    (select table_name
     from information_schema.tables
     where table_schema='my_database'
     limit 1);

I have also tried it without the limit, that was my first attempt:
select *
from table
where table in
    (select table_name
     from information_schema.tables
     where table_schema='my_database');


texts: mysql - select random rows with max 3 rows per parent id

texts:
I currently have a query that returns a maximum of 3 rows per parent_id. I want to enhance the query to return a maximum of 3 random rows per parent_id. This does not work for me at the moment as it returns to me Ascending id's each time I try.

SELECT id, rest_id, rn
FROM (SELECT (@rn := if(@rest_id = rest_id, @rn + 1,
                    if(@rest_id := rest_id, 1, 1)
                   )
         ) as rn,
         meals.*
  FROM meals CROSS JOIN
       (SELECT @rn := 0, @rest_id := '') params
  ORDER BY rand()
 ) meals
WHERE rn <= 3
order by id ASC


texts: Design using multiple tables for 1 booking table

texts:
Forgive if the answer to this is obvious, but I have no clue what keywords to google with.

I'm designing a database for a school that has a variety of IT resources it can lend out (such as iPads, laptops, projectors)

My database keeps track of the resources by using a booking log.
The problem I have is the resources can be booked by either a student, teacher, or a school associate member.

This is my design.

Student, staff and associate members each have their own table, and their own attributes.

How do I design the tables and relationships so the resource hire log keeps track of who's booked the resource?


texts: How restore from .gz backup with new database name using mongorestore r3.2.9?

texts:
I'm trying to backup/restore mongodb database to/from .gz files as sample script here

#01 create .gz backup - ok for r3.2.9 and r3.4.10
mongodump --db ${DB_NAME} --gzip --archive=${BACKUP_FILE_GZ}

#02 restore from .gz file - NOT ok for r3.2.9
mongorestore --gzip --archive=${BACKUP_FILE_GZ} --nsFrom "${DB_NAME}.*" --nsTo "${DB_NAME_RESTORE}.*"

Step 01 i.e. back up is good for both mongodb version r3.2.9 and r3.4.10; though step 02 NOT works for r3.2.9

How can I get mongorestore version r3.2.9 to restore from .gz file and be able to rename the database?

p.s.

We have the solution here but that requires the backup to be a folder; my backup files are huge i.e. 1Gb-2Gb so the extraction is too much time-consuming.


texts:
I have been working around on a query given by one of my developers who works on crystal reports as a junior DBA, I always like to help them even though I am busy with other things as it will increase  my knowledge in T-sql. The current scenario was simple as to do a self join. the schema for this scenario is given below

create table resource(
res_id int,res_name varchar(20), man_id int)

populating the content and running the query below give the resource name and its manager

SELECT a.res_name,b.res_name AS manager FROM dbo.RESOURCE a INNER JOIN dbo.RESOURCE b
ON b.res_id=a.man_id

res_name   manager
------------------
sam       will
sunny     helen
will      micah

but next time my friend entered into my cubicle he need a different answer from the above he needs a result like this

sam will micah

so how will I chain the results I no idea where to start.


texts: MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure

texts:
I have this stored procedure:

    START TRANSACTION;

code_gen_loop: WHILE @Codes > 0 DO

    IF MOD( @Codes, 10000 ) = 0 THEN

        EXECUTE qry_status_stmt2;
        EXECUTE qry_status_stmt3;
        EXECUTE qry_canceled_stmt;

        IF @Canceled = 1 THEN
            ROLLBACK;
            LEAVE code_gen_loop;
        END IF;

    END IF;

    EXECUTE qry_rand_stmt;
    EXECUTE qry_insert_stmt;

    SET @Codes = @Codes - ROW_COUNT();

END WHILE;

IF @Canceled = 0 THEN
    COMMIT;
    EXECUTE qry_complete_stmt;
END IF;

The main loop is made to insert millions of rows into a table. Every 10,000 inserts it updates a memory table with its progress and checks if the job has been canceled. If the job is canceled then ROLLBACK the inserts and break out of the loop. If the job hasn't been canceled then continue the loop until finish and then commit the inserts.

The problem is that if the job is canceled it breaks from the loop correctly but does not rollback the commits. If I go look in the table I can see it has committed the inserts up to where the job was canceled.

Why is my ROLLBACK not working here?

Full procedure can be found http://pastebin.com/2YghzJrE


texts: Stored procedure with ROW instead of a list of parameters

texts:
I have a table with 15 columns, and trying to create a stored procedure in PL SQL that will only insert a row in a table using the INSERT statement.

I would like to know if it is possible to declare the list of IN parameters of the stored procedure as a RECORD structure instead of 15 parameter, so when calling this stored procedure, I will only pass a RECORD structure instead of 15 single variables.

Is that possible in PL SQL? If no, is there any shorthand for doing this kind of declarations? Thanks


texts: How does query parameters work under the hood?

texts:
I thought about how can it work under the hood and I came with an idea

Db engine receives query with parameters and also defined values of those parameters
SQL is being parsed into syntax tree like that

After parsing values of parameters are inserted into tree to places where associated with them parameters are
Query is being executed without another parsing

Is it at least conceptually true?


texts: How to model a relationship with variable tables (polymorphism)?

texts:
What is the best way to model the following kind relationship?

Let's say I have tables representing two types of entities: company and person. I have a customer table with an entity_id field, which should refer to either a company or a person.

If I simply store a company_id or a person_id in entity_id then I won't know which table to look up the entity in. The first solution which has occurred to me is to have a entity_type field in customer which would take the values "company" or "person" to tell me which table my foreign key in entity_id refers to. I'm not sure if this is the best way though.

This seems like it should be a fairly common problem in database design, so I'm hoping there is a design pattern I can adopt.

I've used the word polymorphism in the title in the broader sense, so I'm not referring to inheritance. In my example, a company is not a person (or vice versa) but they are still interchangeable. Other questions I've found on Stack Exchange sites have assumed inheritance is also a requirement, so I'm not sure that there is a suitable duplicate.


texts: solution for non-technical user uploads to SQL Server DB?

texts:
I need a way to get subjective call QA scoring into our business intelligence system.

It currently lives in Excel. The problem with a direct upload from Excel to the DB is that there is no validation or response from the db necessarily (although I guess I can program these in).

Is there a solution that already exists that allows a non-technical user to import data, and already have it validated against the database?

There are myriad data entry software solutions out there, but as far as I know, actually receiving input from sql server (These are the valid values) is something that's not very common.

MS Access is a possible solution - however, for some reason, their data entry forms do not allow for a bulk copy/ paste, which would slow down end users. You have to enter one item at a time.


texts:
I am new to databases, but I hope you can help me to understand something.

Take part of this query:

  select min(date) as date...

Assume that some of the dates are null. What effect would it have? Would the query break?


texts: Mongoimport 'error connecting to db servers' for cluster

texts:
I'm unable to use mongoimport to load some documents to a cluster hosted by MongoDB. I followed this tutorial Load File with mongoimport and then used this command in Powershell to try and load my documents:

.\mongoimport.exe --host "mongodb://clusterads-shard-00-00-qlf9n.mongodb.net:27017,clusterads-shard-00-01-qlf9n.mongodb.net:27017,clusterads-shard-00-02-qlf9n.mongodb.net:27017/admin?replicaSet=ClusterADS-shard-0" --ssl -u <USER> -p '<PASSWORD>' --authenticationDatabase <AUTHENT> --db adscoursework --collection volcanos --drop --jsonArray --file C:\data\volErups.json

This gives the error:

[........................] adscoursework.volcanos       0B/844KB (0.0%)
[........................] adscoursework.volcanos       0B/844KB (0.0%)
Failed: error connecting to db server: no reachable servers

I can connect to the above host using Mongo Shell so I don't understand where the issue is.


texts: DMV CONNECTION_HOST_APPLICATION field is NULL

texts:
I am trying to monitor cubes and expressions of Analysis Services using DMV queries that are stored on Server's RAM.
I found many useful information about connections on connection's table of SSMS's DMVS rowser using the following mdx statement.

SELECT *
FROM $SYSTEM.DISCOVER_CONNECTIONS

The problem is that the [CONNECTION_HOST_APPLICATION] field of connection's table is not recorded and contains null values for my client application.

  CONNECTION_HOST_APPLICATION specifies "The name of the machine that initiated the connection".

As a result i can not filter the rows that are generated from my client tool to analyse only them. So how can i modify my client tool in order to register this field?


texts:
I am replicating our live database using transaction replication. This is basically to have a backup copy of our database in the case of server failure.

The replicated database is read only, so I backed it up, restored it, and tested it.

I noticed straight away that the identity columns didn't work.

For instance, the original schema was like this:

CREATE TABLE [dbo].[Inspection](
[InspectionID] [int] IDENTITY(1,1) NOT NULL,
[CarID] [int] NOT NULL,

Then, on the replicated copy, it looks like this:

CREATE TABLE [dbo].[Inspection](
[InspectionID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CarID] [int] NOT NULL,

That means the identity columns are disabled, right?

Is there a way to get around this and make this database an independent copy in its own right?

Also, I noticed that the foreign keys are not propgated. I am aware that there are settings for this, but will propogating the foreign keys be a problem now that the identity columns are disabled (which are the primary keys)?


texts: data driven report --send max 2 report when records return instead of sending every 10 min

texts:
I have a data-driven report that is scheduled to run every 10 minutes; it sends the report if any records return.

I'd like to change this so the report is sent out at most twice a day.

For example: Currently, let's say the report generates records on the following runs for a given day:

02:10
08:40
12:20
14:40
16:30
21:50

As things stand now, each of those 6 runs will send the report out. If the process I want was in place, it would have sent the 02:10 and the 08:40 reports, and not sent the other 4 (even though records were generated), because it had already sent out 2 reports this day.

The count would restart at 0 reports sent out at midnight every day.


texts:
I'm practically newbie with MySQL.
I'm trying to do a query to retrieve a preformatted date, obtained from a subtraction, using CONVERT.
This is the query:

sql = f"SELECT CONVERT(timestamp - {sqlToday}, getdate(), 23), count_issue FROM total_unresolved_issue WHERE project = '{project}'"

timestamp is a column of the database, and sqlToday is a date in format datetime.date(2020, 2, 13).
Unfortunately this error returns, and I don't know how to handle it:

  mysql.connector.errors.ProgrammingError: 1064 (42000): You have an
  error in your SQL syntax; check the manual that corresponds to your
  MariaDB server version for the right syntax to use near 'getdate(),
  23), count_issue FROM total_unresolved_issue WHERE project = 'Graphic'
  at line 1

Thanks in advance!


texts: What could explain a 30 second MySQL SELECT query latency when profiling records an execution time of <1 second?

texts:
I'm trying to figure out why a simple select with a LIMIT 1 clause (admittedly, on a really bloated table with a lot of rows and indices) is sometimes taking 30+ seconds (even 2 minutes, sometimes) to execute on an AWS RDS Aurora instance. This is on a writer instance.
It seems to occur for the first query from a client, only on a particular select that looks through hundreds of thousands of rows, and only sometimes.
The query is in the form:
SELECT some_table.col1, some_table.col2, some_table.col3, some_table.col4,
  MAX(some_table.col2) AS SomeValue
FROM some_table
WHERE some_table.col3=123456 LIMIT 1;

And 'explain' outputs:
+----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------+
| id | select_type | table         | type | possible_keys | key     | key_len | ref   | rows   | Extra |
+----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------+
|  1 | SIMPLE      | some_table    | ref  | col1          | col1    | 4       | const | 268202 | NULL  |
+----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------+

I managed to reproduce the issue and captured the profile for the query in PhpMyAdmin. PhpMyAdmin recorded the query as taking 30.1 seconds to execute, but the profiler shows that execution itself takes less than a second:

So it looks like the execution itself isn't taking a lot of time; what could be causing this latency issue? I also found the same query recorded in RDS Performance Insights:

This seems to occur for the first query in a series of identical or similar queries. Could it be a caching issue? I've tried running RESET QUERY CACHE; in an attempt to reproduce the latency but with no success. Happy to provide more information about the infrastructure if that would help.
More info
SHOW VARIABLES LIKE 'query_cache%';

SHOW GLOBAL STATUS LIKE 'Qc%';

Rows examined and sent (from Performance Insights):

SHOW CREATE TABLE output:
CREATE TABLE `some_table` (
`col1` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col2` int(10) unsigned NOT NULL DEFAULT '0',
`col3` int(10) unsigned NOT NULL DEFAULT '0',
`col4` int(10) unsigned NOT NULL DEFAULT '0',
`col5` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`col6` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`col7` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`col1`),
KEY `col2` (`col2`),
KEY `col3` (`col3`),
KEY `col4` (`col4`),
KEY `col6` (`col6`),
KEY `col7` (`col7`)
) ENGINE=InnoDB AUTO_INCREMENT=123456789 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


texts: UPDATE with JOIN: Incorrect syntax near the keyword 'RIGHT'

texts:
I'm using SQL Server Management Studio 2005. I want to look up a column value in another table, using a shared ID tblOrder_ordernr in both tables, but only update one table.

To me, the query is strangely written (UPDATE tblTid but SET viewOrderAlla...), but this is what I have to work with. How would I rewrite this query to do the same?

UPDATE tblTid RIGHT JOIN viewOrderAlla ON tblTid.sOrdernr = viewOrderAlla.tblOrder_ordernr
SET viewOrderAlla.tblOrder_verkligtid = 0
WHERE viewOrderAlla.tblOrder_verkligtid<>0 AND tblTid.iTidID Is Null
AND viewOrderAlla.tblOrder_levdatum>=DATEADD(month,-6,GETDATE())

I've tried moving the SET to before the RIGHT JOIN as per some other questions here, but I find no answer that even lets me parse the expression without a syntax error.

The tables are linked into an Access 2013 database, and there, the query runs fine. But not in SQL server.


texts: Whether or Not to Use DataPump with Oracle GoldenGate

texts:
We are setting up GoldenGate to keep 3 of our Oracle databases in sync. Regarding configuration:

We plan to have an instance of GoldenGate running on all 3 nodes. Each node will run an extract on (local) Transaction/Redo Logs to create local Trail files, then run Replicat (locally) to the other 2 databases.

These connections are using TCP/IP so there should not be any lost packets? If Replicat running over the network sends and fails, will GG reattempt until it does or just move on? (Resulting in out of sync?)

VS

DataPump/ Checkpoint process seems to be industry standard for replication over the network remedying the above process.

What are the advantages to using the DataPump/Checkpoints in this process vs what I described above?

Does the Datapump/Checkpoint process add additional syncing for redundancies ?


texts: My SQL server showing different result with same query execution at different time

texts:
When I execute below query from MySQL server, it shows me different output for each time even though database is stable and not used by anyone except me.

SELECT TABLE_NAME , table_rows
FROM information_schema.tables
WHERE table_schema='DBNAME'
AND TABLE_TYPE != 'VIEW'

MySQL Server Version: 5.1

I am quite confused with this behaviour.


texts: Need to delete consecutive, unchanged records

texts:
I have a rather large MySQL db that is tracking pricing changes for a large number of products.  Each entry has a SKU a datetime and a price.  Sometimes, there is no change in the price from one time period to the next.

I am trying to figure out a way to more efficiently store this data.  I would like to delete any instances where the price didn't change.  For example, if a product was $10 on march 10, 13, and 16th, then it went to $12 on the 19th, then we have no need to store those duplicate prices on the 13th and 16th.  We can just delete those entries, and when presenting data, we will assume no entry for those dates means no change in price.

I am thinking the best way to do this is to run a script to clean up the table and remove these duplicates every day or so.  Would there be any way to create a mySQL query that can do this?  Otherwise, I guess I'll just need to write a script that will cycle through the whole table and do it with multiple queries.

edit:  more detail

Here is my table:

+----+-----+----------------+-------+
|   id | sku |  date             | price |
+----+-----+----------------+-------+

id is the auto-increment primary key.


texts: Running two DELETE statements causes a syntax error

texts:
DELETE `member`
    FROM `member`
    WHERE `member`.`type_id` = 1;

    DELETE `subscriptions`
    FROM `subscriptions`
    WHERE `subscriptions`.`type_id` =  1;

  Table: subscriptions Columns: id  int(11) PK AI type_id   int(11)

  Table: member Columns: id int(11) PK AI type_id   int(11)

With the above I seem to be getting the following:

 Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE `subscriptions`
        FROM `subscriptions`
        WHERE `subscriptions' at line 5:

I think the problem that I am having is that I am trying to run both queries at the same time. I have also put this through a syntax checker and it gives the following: screenshot.

On a side note notice that both tables contain the attribute type_id. I did try to condense this by using an inner join:

DELETE FROM member, subscriptions
USING member
INNER JOIN subscriptions ON type.id = type_id
WHERE type.id= 1;

But I believe the above inner join isn't correct


texts: postgresql, select sum of rows for multiple ids

texts:
I have a query with sums I'd like to repeat with variables which are day and consumption_sector.name. I don't know how to simplify my question enough not to lose the point I need. Programatically I'd like to have variables and a loop all into one only select result.
Where I am is with having one result line, I'd like to customize and do multiple for having for example all month of year of give date, or all results for a given date for multiple consumption_sector.name
Result

Query I reached
set search_path to dev;
SELECT
    (SELECT SUM(total)
        FROM daily_unit_big_int
        INNER JOIN measuring_point on measuring_point.id = daily_unit_big_int.measuring_point_id
        INNER JOIN consumption_sector on consumption_sector.id = measuring_point.consumption_sector_id
        WHERE
        measuring_point.type='devices_logger' AND -- an enumerate
        consumption_sector.name like '%gnacio%' AND
        day = '2020-10-25') AS daily_total, -- a date I'd like to give as parameter/variable
    (SELECT SUM(total)
        FROM daily_unit_big_int
        INNER JOIN measuring_point ON measuring_point.id = daily_unit_big_int.measuring_point_id
        INNER JOIN consumption_sector ON consumption_sector.id = measuring_point.consumption_sector_id
        WHERE
        measuring_point.type='devices_logger' AND
        consumption_sector.name like '%gnacio%' AND
        EXTRACT(MONTH FROM day) = '10' AND
        EXTRACT(YEAR FROM day) = '2020'
        ) AS monthly_total,
    (SELECT SUM(total)
        FROM daily_unit_big_int
        INNER JOIN measuring_point ON measuring_point.id = daily_unit_big_int.measuring_point_id
        INNER JOIN consumption_sector ON consumption_sector.id = measuring_point.consumption_sector_id
        WHERE
        measuring_point.type='devices_logger' AND
        consumption_sector.name like '%gnacio%' AND
        EXTRACT(YEAR FROM day) = '2020'
        ) AS yearly_total
    ;

Minimal example with pseudo code to understand the point
$variable_date='2020-10-25'
for $consumption_sector_id in (12,13,46,72) do
    SELECT consumption_sector.id AS consumption_sector_id, SUM(total) AS daily_total
            FROM daily_unit_big_int
            INNER JOIN measuring_point on measuring_point.id = daily_unit_big_int.measuring_point_id
            INNER JOIN consumption_sector on consumption_sector.id = measuring_point.consumption_sector_id
            WHERE
            measuring_point.type='devices_logger' AND -- an enumerate
            consumption_sector.id = $consumption_sector_id AND
            day = $variable_date -- a date I'd like to give as parameter/variable
end--for

Result
|consumption_sector_id|daily_total|
|12|100|
|13|1000|
|46|1000|
|72|15468|

Here a fiddle for the example where I retreive all data for a particular day and a particular consumption_sector_id


texts: Dynamic Folder Creation when Backing up to Azure BLOB Storage

texts:
Is there a way to dynamically create folders in BLOB so each DB goes into its own folder?  I know in Azure BLOB Storage they are not real folders, but I hope you get my point.  In essence I am using the @URL instead of @directory

I am backing up (TLog) 30+ DB's every 5 minutes, and it is very cumbersome to manage in BLOB.

Any insight or help would be appreciated.

Steve


texts: Subquery uses ungrouped column "s.student_id" from outer query

texts:
I have the following tables:

1 teacher can have 1 or more students
1 student can have 1 or more homeworks

I would like to know in a list of teachers, for each teacher if it has any student with any homework assigned.

Query I tried:

select t.teacher_id as id,
       t.name as name,
       (select count(*)
          from homework
            where s.student_id = h.student_id ) as teacherHasHomeworkToVerify
from teacher t
       left join student s on t.teacher_id = s.teacher_id
       left join homework h on s.student_id = h.student_id
       group by t.teacher_id
       order by t.name COLLATE "en_US"

Error:

  ERROR: subquery uses ungrouped column "s.student_id" from outer query

SQL Fiddle link: http://sqlfiddle.com/#!15/a00ac/4

Schema:

create table teacher (
    teacher_id serial primary key,
    name text
);

insert into teacher (teacher_id, name)
values (1, 'bob'),
       (2, 'mary');

create table student (
    student_id serial primary key,
    teacher_id bigint,
    name text
);

insert into student (student_id, teacher_id, name)
values (1, 1, 'smith'),
       (2, 2, 'jimmy'),
       (3, 2, 'james');

create table homework (
    homework_id serial primary key,
    student_id bigint,
    subject text
);

insert into homework (homework_id, student_id, subject)
values (1, 3, 'math');


texts: How Do I Find Who Last Edited a Record or Table in a Database?

texts:
I believe that I have most of the work done for whoever wants to tackle this question.

I have the following query built:

USE MyDataBase;
GO

SELECT DB_NAME(ius.[database_id]) AS [Database],
       OBJECT_NAME(ius.[object_id]) AS [TableName],
       MAX(ius.[last_user_lookup]) AS [last_user_lookup],
       MAX(ius.[last_user_scan]) AS [last_user_scan],
       MAX(ius.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius

WHERE ius.[database_id] = DB_ID()

GROUP BY ius.[database_id], ius.[object_id];

If possible, how do I add which user it was that made a change?


texts:
I install postgresql-12.4 on my win pc. When i'm trying to enter into admin panel (pgadmin) i cathcing message:
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5433? could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5433?
I have 2 users on my pc. Admin and another user with adming rights. Under admin profile pgadmin works good, uder another user profile pgadmin throws error message. In services server is running.


texts: how to get last date of today in datetime datatype?

texts:
select dateadd(s, -1, dateadd(month, datediff(month, 0, getdate())+1,0))

when i execute this query, it gives '2012-01-30 23:59:59.000'. I donno how to get it as '2012-01-30 23:59:59.997'. If anybody Know, please say.

thanking you!!!


texts:
I am moving our current database server from a regular EC2 instance running mysql to a RDS instance. I moved all the databases using the amazon-suggested method, simply piping the mysqldump output to a mysql client connected to the remote database (RDS instance). Everything worked fine for most databases, except one. After searching, the cause is a big, 13 Gb table that starts copying OK, but after some time the mysql client throws an error (sadly I didn't take note of the error code when it happened). It was "Connection lost" for sure.

This is the table:

CREATE TABLE `UniqueTextLookup` (
    `UniqueTextLookupID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TextID` CHAR(32) NOT NULL COLLATE 'ascii_bin',
    `CrcTextID` INT(10) UNSIGNED NOT NULL,
    `UniqueText` CHAR(255) NOT NULL,
    `CrcUniqueText` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`UniqueTextLookupID`),
    UNIQUE INDEX `TextID` (`TextID`),
    INDEX `UniqueText` (`UniqueText`(10)),
    INDEX `CrcTextID` (`CrcTextID`),
    INDEX `CrcUniqueText` (`CrcUniqueText`)
)

I suspect that the table size has something to do with the indexes more than the data (it's just a lookup table for unique texts, with some hacks to accelerate queries using CRC32 of some columns). It has 15 millon rows.

Any Ideas? I'm running the dump+pipe again to pinpoint the error code.

Some notes:

I cannot login by ssh to the RDS instance, so dumping to a file and copying by scp to the RDS server is not an option.

EDIT:

Checking with mytop It looks like this query (one of the lasts of the dump) :

/*!40000 ALTER TABLE `UniqueTextLookup` ENABLE KEYS */

gets stalled, it has been running for 322 seconds and counting...


texts: SQL Server - How to achieve READCOMMITED and NOLOCK at the same time?

texts:
If I have a User table:

id | name   | age
1  | Mateus | 27

The first transaction executes an update, and leaves the transaction open, without committing or rolling back:
update User set name = 'John' where id = 1;

Meanwhile, the second transaction executes a select:
select * from User where id = 1;
This command will wait until the first transaction releases the lock, either by commit or rollback, unless the second transaction uses a table hint with(nolock), like so:
select * from User with(nolock) where id = 1;
That will return the record without locking the transaction, but it will return the uncommitted value John instead of the original Mateus.

From what I know, there are only two ways to return a locked record without locking the current transaction, one can use with(nolock) that will return the record but with the uncommitted value, and with(readpast) that will just not return the record.

Is there a way I can return the record, without locking the table, and returning its "old" values?


texts: How to use a avg expression in the WHERE between clause?

texts:
I want to compare two value with avg rating of user

select * from dt_tutor_comments where avg(rating_value) BETWEEN 1 and 2 GROUP by u.id


texts: Trying to trace error in Stored Procedure using XEvents

texts:
Here is the situation:

I have a SQL server ETL application with a stored procedure that is inserting/updating some data into a table.  The table has 1 INT column (it is an identity column and there is no inserting of values into that column by the stored procedure) and the rest of the columns are VARCHAR's.  In the catch block, the code is logging the stored procedure and Error_Message() to a logging table.

The error message that is being logged is:
The conversion of the VARCHAR value '010234567898789' overflowed an int column.

So, I am attempting to find out how to use XEvents to see the exact INSERT/UPDATE statement that the error is really occurring in, but I cannot seem to find the Event that I need to look at to see this.

Can anyone please either point me to a good tutorial or tell me what event(s) that I need to look at to see the INSERT/UPDATE SQL code and the associated error Event?  I know it can be done using Profiler, but I was hoping to be able to use XEvents since it is supposed to be much lighter and this is a production server were the issue is occurring.

Thank you.


texts: Reflexivity axiom for inferring functional dependencies

texts:
As you know there are three Armstrong's Axioms for inferring all the functional dependencies on a relational database. (X, Y and Z are set of attributes)

Reflexivity: If X ⊆ Y, then Y → X
Augmentation: If X → Y, then XZ → YZ for any Z
Transitivity: if X → Y and Y → Z, then X → Z

I understand the augmentation and transitivity for example if we had such schema:

SOME_SCHEMA(a, b, c, d)

with such functional dependencies:

a → b
b → c

By using augmentation we could get ac → bc or by using transitivity we could get a → c and much more, however I am not sure how to infer more functional dependencies using reflexivity axiom? What does it really mean that some attribute is a subset of some other attribute?

Could you show me an example using my schema or creating your own, please?

Thank you in advance!

P.S. Not sure what tag I should use for this question, feel free to change it and remove this line.


texts: How do Availability Groups work with cheduled jobs and SSIS packages?

texts:
I have 2 SQL Servers set up with a database in an AlwaysOn Availability Group.

I also have jobs set up which run against the database. During a failover how do I ensure the jobs will continue to run on the secondary server? Do I need to install the jobs and SSIS packages on both machines and manually disable them on the secondary machine... then manually enable them in the case of a failover? Or is there built in functionality to handle this?


texts: How to use validation rule within database with drop down menus.

texts: Building a scheduling database using access 2013 for my company. I currently have it set up so that only employees that have a rating of 3 or more for the particular job show up when you use the drop down menu. However since we have several associates that meet that criteria for multiple positions there is the risk that the supervisors may double schedule someone. I am wondering if there is a way I can have a dialogue or warning pop up if the same name is added to the schedule in two places. If you need more information please let me know! I feel like this is way easier than I am making it.


texts: Getting rid of partitioning in Postgresql 9

texts:
it turns out that our approach with list partitioning was not the best idea. We have  way to much small partitions and a lot of time is wasted on partition choosing by query optimizer.

What's the easiest way to merge all partitions back into one table? I don't expect any data clash problems.

<!-- language: lang-sql -->
PARENT_TABLE(id,col1,col2,col3,client_id)

CREATE TABLE CHILD_TABLE_1 (CHECK (client_id = '1')) INHERITS (PARENT_TABLE);
CREATE TABLE CHILD_TABLE_2 (CHECK (client_id = '2')) INHERITS (PARENT_TABLE);
CREATE TABLE CHILD_TABLE_3 (CHECK (client_id = '3')) INHERITS (PARENT_TABLE);


texts: Diff in Transactional and Non-transactional tables

texts:
What is the difference between Transactional and Non-transactional tables? And How will the sql query go if I want to create them?

Also when to use transactional or non-transactional tables?.


texts:
I'm trying to import file that contains Chinese characters into a table. File encoding is Big5 (Traditional).

Here is a sample file like one that I need to import: https://www.pastiebin.com/5d7782d9b63fa

Table where file has to be imported into has such a structure:

create table dbo.Test (
      AccountId   numeric(18, 0) not null
    , Province    nvarchar(50)       null
    , City        nvarchar(50)       collate Chinese_Hong_Kong_Stroke_90_CI_AI
    , Country     nvarchar(50)       null
    , Gender      nvarchar(50)       null
)

If I import using OPENROWSET/BULK then all the data is transferred correctly:

select AccountId, Province, City, Country, Gender
from openrowset (
      bulk 'C:\chinese_sample.dat'
    , firstrow = 1
    , formatfile = 'C:\chinese_sample.xml'
) t

Here is a format file that I use: https://www.pastiebin.com/5d7783396f9e4

But if I try to import file using SSIS, then Chinese characters are not parsed correctly.

In Flat File Source I use DataType string [DT_STR] and CodePage 950. Then convert it to Unicode string [DT_WSTR].

Here is how text is imported into table:

As we can see some characters are parsed correctly and some are not.
What am I missing?


texts: Does SQL Server 2017 CU1 break clusterless availability groups?

texts:
Background:

My division is is doing an upgrade from SQL Server 2008R2 with mirroring to SQL Server 2017 with clusterless availability groups. Until recently the testing has surfaced no issues or red flags. Then we installed CU1, had issues, uninstalled CU1 and the issues went away. The OS is server 2016 with the latest patches.

Observed behavior after CU1:

Using either SSMS or tsql we could create a 2 replica clusterless synchronous availability group, and add one database to it. The group could be failed over multiple times without issue. Ah, but add a second database and issues would arise on the failovers. One of the databases would invariably wind up in a not synchronizing state. No amount of fiddling could resurrect it. If I dropped and recreated the whole thing, it might be the other database that went to not syncing. A pertinent error message in the logger was "Failed to update Replica status due to exception 35222." This seems to be a message related to clusters, but since we are clusterless I was confused. After we uninstalled CU1 on both replicas, I was able to create the AG and add 22 databases (including the two original). Failovers were without issue. On a side note, automatic seeding did not always work with multiple databases. The operation would fail with a "Seeding Check Message Timeout". Dropping those databases from the AG and adding them one at a time was successful.

My question is:

Has anyone else experienced issues with clusterless AGs after CU1? If so, were you successful where I was not?

Comment/opinion:

I thought CUs were going to be tested at the same level as SPs. While I know that bugs creep in no matter how thorough the testing, having this happen on the first one is troubling. It will cause us to really stress test each CU before deploying, which will mean we do not deploy them as they come out. We will deploy them only when we think there is a need to. We are a small organization without a dedicated dba, and need to be selective about what take on.


texts: Select distinct id with max date according to department

texts:
I have a table such as following

RevNo  RevContent       PIC      Created
-----  ---------------  -------  ----------
00     Testing Purpose  Smith    2008-01-11
01     Testing Purpose  Windsor  2008-02-01
02     Test             Thorn    2008-01-05
02     Testing          Baker    2008-03-01
03     Testing only     Sykes    2008-01-20

I want the output like below, it display the latest date for each rev no. and other details

RevNo  RevContent       PIC      Created
-----  ---------------  -------  ----------
00     Testing Purpose  Windsor  2008-02-01
01     Testing          Baker    2008-03-01
02     Testing only     Sykes    2008-01-20

But when I run the sql, it display the value with the latest date only like below, I want it display the latest date for each rev no. and it based on department(session).

RevNo  RevContent  PIC    Created
-----  ----------  -----  ----------
01     Testing     Baker  2008-03-01

What I have tried:

SELECT CCSMASTERLISTREVNO, CCSREVCONTENT, CCSPREPAREDREV, CCSREVEFFECTIVEDATE
FROM CCS2_TBL_MASTERLIST a
WHERE CCSEQUIPMENTDPMT = :DPMT
AND CCSREVEFFECTIVEDATE = (SELECT MAX(CCSREVEFFECTIVEDATE) FROM CCS2_TBL_MASTERLIST GROUP BY CCSMASTERLISTREVNO HAVING CCSMASTERLISTREVNO =a.CCSMASTERLISTREVNO)
ORDER BY CCSMASTERLISTREVNO DESC;


texts: I need to find mismatches between tables

texts:
So my problem is basically that I have an accounting table that has transaction references, and supporting document numbers.  Some of the document numbers have become mismatched from their actual transaction number.  The worst part is that the bad document numbers are actually valid, but reference a bad transaction. So here's the important part:

Every transaction has two parts.
Reference#, Type (Type A and Type B), and a document number.

I need to find all transactions where either (REF#, Type A)'s DOC# doesn't equal (REF#, Type B)'s DOC#, or where there is no matching Type A or Type B transaction at all.


texts: SQL Master Data Services database has huge leaf member staging table - can I purge old records?

texts:
One of our SQL instances has a large MDS database, and virtually 100% of the size is due to a 300+ million row table named stg.Stg_Project_Leaf. It recently started blowing up our weekly index rebuilds, so I need to figure this out.

From what I can tell, this is a Leaf Member Staging Table for a "Project" entity, set up and automated (by a prior consultant) as a part of an ETL process that builds BI cubes/reports.

Looks like it has been processing about 10k rows per half hour for the past several years and has never been purged. When I check the ImportStatus_ID, though, I see 0 rows waiting to be processed. (Most are 1 - succeeded, less than 1% are 2 - failed.)

Can I just TRUNCATE this table?

This system is being used in an automated fashion, its not like people are logging into the MDS console and individually monitoring and rolling back changes. I found some references to stored procedures that purge these tables (mdm.udpStagingClear or maybe mdm.udpDeletedMembersPurge), but I'm not sure which of those is the one I'd need, nor do I know if they would blow up the log if they tried to do 300m rows at once.


texts:
When running a query including the Actual Execution Plan, the root operator (SELECT) tells me that the Cached Plan Size is 32KB.

A query that joins sys.dm_exec_cached_plans and sys.dm_os_memory_objects, looking at the plan in question, says that values for pages_in_bytes and max_pages_in_bytes are 32768 (32KB), which matches the cached plan size.

What I don't understand is what the value in sys.dm_exec_cached_plans.size_in_bytes, which is 49152 (48KB) stands for. I've read BOL on all these columns, and especially size_in_bytes which says:

  "Number of bytes consumed by the cache object."

I can't get that last bit of the puzzle in place, to understand what it really means.

I know that all operators (not talking about the additional memory grant used for sorts and hashes) requires some amount of fixed memory, to store state, make calculations etc., which is stored with the optimized plan in the cache, but where?

So, my questions are:

What does size_in_bytes really mean
Why is it a higher value than "Cached plan size"?
Where is the fixed amount of memory for all operators/iterators reserved, is it with the "Cached plan size" (32Kb in my example), or anywhere else?

I know they're different DMVs with different functions, but they are related. The compiled (cached) plans in sys.dm_exec_cached_plans joins sys.dm_os_memory_objects on memory_object_address column. The reason I post the questions here, is that I'm asking for help on this, understanding how to interpret the DMVs and their columns.

If size_in_bytes is the cached plan size, why does SQL Server say another value in actual execution plan?

New query, new numbers:

Actual plan

Cached Plan Size 16KB
CompileMemory 96KB

DMVs:

sys.dm_exec_cached_plans.size_in_bytes 24KB
sys.dm_os_memory_objects.pages_in_bytes, .max_pages_in_bytes 16KB.

Also, note that this query doesn't require any additional memory grants for sorts and hash operations.

Microsoft SQL Server 2012 - 11.0.5343.0 (X64)


texts: Order by custom filter without certain data

texts:
I have a similar issue to the question in:
ORDER BY using custom priorities for text columns
...except that I want to only get the data out if I have certain values.  To clarify, we have a table that stores both email addresses and phone numbers in the same column.
I cannot change this structure without breaking a critical system that I do not have source code access to. I need to be able to retrieve only email addresses, and have the query show NULL when there is no email address.
The table structure is:

ID (Int)
Description Code (Int) (can use same values for both emails and phone numbers, nothing distinguishing here)
Number (varchar)

Right now, similar to the question above, I have my SQL as:
order by case
    when con2.NUMBER like '%@ourdomain.com' then 1
    when con2.NUMBER like '%@%' then 2
    else 3
    end

I would like to not show phone numbers in this output.  Is there an easy adjustment to my code to facilitate this?
Example

Records for Sally
ID    Type     Number
1     1011     (215)555-1213
2     1011     (215)555-1312
3     1012     sally@ourdomain.com
4     1013     sallyf@gmail.com

Records for Frank
ID    Type     Number
5     1013     frankiebaby@yahoo.com
6     1013     franks@ibm.com

Records for Julius
ID    Type     Number
7     1010     555-123-9876
8     1011     425-369-1470

Output would return sally@ourdomain.com for Sally and frankiebaby@yahoo.com for Frank, but NULL for Julius.
I need to prioritize one domain over any others, so addresses in our own domain would show up if it's available, if not, then google, hotmail, or whatever else comes secondary.


texts:
I just saw a note about a security issue in MySQL so thought that I'd better patch my test server just in case. The MySQL site is not very helpful and a quick google revealed lots of people asking how to patch MySQL and no answers that I could find.

Does anyone know if it's possible to patch the Community Edition of MySQL or is the assumption that it requires a full install of whatever the latest version happens to be?


texts: What are these generic looking activities from sp_who2?

texts:
I am trying to determine whether a sql server instance is actually in use, or just sitting there. It has no user databases, just the normal system databases and distribution. I'm fairly sure there is nothing happening, but it is in our production environment, so I can't just switch it off without being sure.

I have set up an agent job to run sp_who2 and log to a table every minute to see if I can catch any activity. I have the following rows, but nothing else (except stuff with my name on it!). They all look fairly generic - like it's the agent doing it's thing - but I'd just like to check first.

Are these jobs likely to be anything more than generic jobs doing nothing except checking to see if there's something to do?

DBName  Command ProgramName
msdb    AWAITING COMMAND    SQLAgent - Email Logger
master  SELECT INTO         SQLAgent - TSQL JobStep (Job 0x988FC626DDAAEE4D8F49FC83540F48A5 : Step 1)
msdb    AWAITING COMMAND    SQLAgent - Alert Engine
msdb    AWAITING COMMAND    DatabaseMail - SQLAGENT - Id<19792>
msdb    AWAITING COMMAND    SQLAgent - Generic Refresher
msdb    AWAITING COMMAND    SQLAgent - Job invocation engine
msdb    SELECT              SQLAgent - Alert Engine
msdb    AWAITING COMMAND    SQL Management
msdb    AWAITING COMMAND    SQLAgent - Job Manager
msdb    UPDATE              SQLAgent - Schedule Saver


texts:
I'm trying to do a simple health check for keepalived and other services via a python script and psycopg2 library. All seems to be alright, until I close the connection, at which point a packet with TCP reset is sent.

Due to frequency and amount of services using psycopg2 in our environment, this has become very problematic and creates extensive noise in monitoring.

Script

import psycopg2
import sys

def check():
    # Relying on .pgpass for password
    con = psycopg2.connect('user=monitoring dbname=postgres host=127.0.0.1')
    cur = con.cursor()
    cur.execute("select 'keepalived healthcheck'")
    cur.close()
    con.close()

try:
    check()
    print("ok")
except Exception as e:
    print("not ok")
    print(str(e))
    sys.exit(1)

tcpdump
tcpdump -v 'tcp[tcpflags] & (tcp-rst) != 0' -ilo

16:27:45.307006 IP (tos 0x0, ttl 64, id 8123, offset 0, flags [DF], proto TCP (6), length 40)
    localhost.40797 > localhost.postgres: Flags [R], cksum 0x0cca (correct), seq 3830516781, win 0, length 0

From PG side of things, all seems to be OK

2019-04-23 16:27:45.300 CEST process=15615 c= t=0 s=5cbf20e1.3cff [unknown]@127.0.0.1:[unknown] app=[unknown] LOG:  connection received: host=127.0.0.1 port=40797
2019-04-23 16:27:45.304 CEST process=15615 c=authentication t=0 s=5cbf20e1.3cff monitoring@127.0.0.1:postgres app=[unknown] LOG:  connection authorized: user=monitoring database=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)
2019-04-23 16:27:45.306 CEST process=15615 c=BEGIN t=0 s=5cbf20e1.3cff monitoring@127.0.0.1:postgres app=[unknown] LOG:  duration: 0.095 ms
2019-04-23 16:27:45.306 CEST process=15615 c=SELECT t=0 s=5cbf20e1.3cff monitoring@127.0.0.1:postgres app=[unknown] LOG:  duration: 0.234 ms
2019-04-23 16:27:45.306 CEST process=15615 c=idle in transaction t=0 s=5cbf20e1.3cff monitoring@127.0.0.1:postgres app=[unknown] LOG:  disconnection: session time: 0:00:00.006 user=monitoring database=postgres host=127.0.0.1 port=40797

I've tried various combinations of OS, psycopg2 and PG versions to no avail.

OS: Centos 7 and Ubuntu 18.04
psycopg2: From latest down to 2.7.x
PG: Latest 11 down to 9.4 (we use 9.4-bdr currently in production)

Has anyone experienced this and could share any advice how I could prevent this from producing network packets with TCP [R] flag ?

Perhaps I'm misusing this library ?

Edit: After further testing and reproducing under different conditions, this seems to be happening when SSL connection is being used.

Edit2: To clarify the importance of this problem, this is the two nodes in the cluster and their tcp reset rate. The expectation here is to have very low rate, so appropriate alerts can be set.

If we set up alert thresholds to high values, we may miss out on actual problems in the cluster, i.e. MIT attacks etc.