texts: Transactional Replication - Replication Order of Related Tables

texts:
** This was previously asked on Stackoverflow - I removed it from there and recreated here **

I have an issue with SQL Server Transactional Replication. My topology is as follows (and this cannot be changed, so I'm trying to work within the limitations that it has).

I have a publisher database (SourceDB) that is used by a new application. Some of the data entered there needs to be made available to an old application that is being replaced by the new one; the old application uses TargetDB, (the data in the TargetDB will be read only and used for reporting and downstream feeds). Both databases contain broadly similar data, but structured very differently. Data needs to be available as close to real time as possible. We have a remote distributor.

So we have set up a proxy version of SourceDB which acts as a subscriber (SourceProxyDB). This contains the subset of tables required for replication. Hanging off these tables are triggers which will transform the data and enter it into TargetDB.

SourceDB and the remote distributor are both SQL Server 2012 SP1; TargetDB is SQL Server 2008R2 SP2.

So basically:

data entered in SourceDB
transactional replication moves it to SourceProxyDB
triggers transform and enter data in TargetDB

So far, so good. For the most part this has been working well... until we started seeing some issues as follows:

There is a parent-child table relationship in SourceDB that needs to get into TargetDB
Typically one parent record is created followed by around 130 child records
Each child record contains a reference to the parent record's ID, which is an identity column
Sometimes we see the parent record arrive first in SourceProxyDB, followed by the child records as expected
Sometimes we see the parent record arrive out of order; either after all the child records arrive, or in the middle of the set of child records
There is no pattern to this
When the child records arrive first, they all contain a reference to the parent record which follows them, implying that the records are entered in the correct order in SourceDB but replicated in the wrong order
All child records always arrive in the correct sequential order; the only record out of order is the parent one

What we know/have tried so far:

confirmed that the parent and child records are added to SourceDB in a single transaction
used SQL Profiler to confirm that the parent and child records are added to SourceDB in the correct order
used SQL Profiler to confirm that the parent and child records are replicated to SourceProxyDB in the wrong order
added a FK relationship to SourceDB to tie the parent and child records together
marked the FK relationship 'False' for 'Not for Replication'
reinitialized replication for the parent and child tables (note - after doing this, the FK didn't appear in SourceProxyDB, which made me suspicious)
set SubscriptionStreams to 1

... and I'm still periodically seeing the parent record replicated either after or in the middle of the associated child records at SourceProxyDB.

We're not seeing any FK errors or violations at the subscriber (SourceProxyDB) - as noted above, the FK didn't make it to SourceProxyDB after replication was re-initialized. The nature of the logic in the triggers requires the parent record to be available to the child records however so we are seeing logic errors.

I was under the impression that transactional replication would preserve the order of insertion of records, particularly those with FK relationships.

I'm at a real loss as to where to look next. Any insights as to why this is happening and any possible solution(s) will be gratefully received; I've been tearing my hair out for days over this now...


texts:
SQL Server 2016 continually crashes when running an ETL source query. Memory starts consuming at a fantastic rate then hits max and the service shuts off. This seems ridiculous, especially since the query runs perfectly on SQL Server 2014 with less memory and slower disks. <What could have changed and more importantly, is there a way to change server query or memory settings in 2016 that will prevent this crash scenario? Can anyone narrow the issue (and confirm it's a bug) or provide a more robust work around?

MAXDOP is set to 4, min memory is 8000 max is 24000 with 28gb on the system. The SQL 2014 machine had SharePoint running on it also (different SQL instance), and as a result has a lower max memory (12533mb). The SQL 2016 machine is an exact copy except it does not have the SharePoint install and was upgraded to SSDs.

The query only has 9 columns (6 INTs) for 7,122,737 records with two small table joins and minimal filtering... there's nothing that can change with the query and again it was working perfectly on SQL 2014. In fact, it runs a minute faster in SQL 2014 vs SQL 2016 despite the SSDs and almost twice the memory additions to a supposedly faster release!

So after a lot of searching, multiple different installs reverting to a database only install in default locations I have mostly identified the issue. One of the columns being hashed is a VARCHAR(MAX) which triggers a massive memory request from HASHBYTES. Not entirely unreasonable, even though the longest column is actually only 49 characters. Source system... work around will be to cast it dynamically I think.

If SQL Server reaches max memory, which it quickly arrives at with this query it crashes. This seems hit and miss, and may generate other errors instead, leaving the server up. Adding DISTINCT ensures the crash behavior unless excessive amounts of memory are present.

I've created a Microsoft Connect issue here if you have additional testing or ideas or can reproduce the issue. I'd say this is only partially the answer as it is still merely narrowed to a range of possibilities, that I don't have the time or capability to fully identify. However, it does not appear to be general memory pressure or a result of general SQL 2016 setup and services.

The following can reproduce the issue.

CREATE TABLE Testing (
    VarCharMAX VARCHAR(MAX) NULL,
    VarCharRealistic VARCHAR(100) NULL
) ON PRIMARY
GO

SELECT TOP 1000000
    IDENTITY(INT, 1, 1) AS Number
INTO Numbers
FROM master.dbo.syscolumns x
CROSS JOIN master.dbo.syscolumns
GO --Ensure 1 million rows were inserted

INSERT INTO Testing
        (VarCharMAX, VarCharRealistic)
SELECT
     CAST(Number As VARCHAR(MAX)) + 'a' AS VarCharMAX
    ,CAST(Number As VARCHAR(100)) + 'a' AS VarCharRealistic
FROM Numbers
GO

--Server crash under ~16GB memory
SELECT DISTINCT
    HASHBYTES('md5', VarCharMAX)
FROM dbo.Testing


texts:
We have a main SQL Server which replicates to two backup servers via continuous merge replication.

There are regular updates to the schema. Regular changes include:

Adding new tables and relationships with existing tables
Changing the data type of columns on tables already replicated

What is the easiest way to maintain replication in such an environment?

If changes are made to the publisher, some changes cannot be synced to the subscriber and the whole replication has to be setup again (agent + subscriptions).

These databases are too big to replicate in its entirety every time there is an update.

Any advice will be appreciated.


texts: Postgres to Postgres foreign data wrapper, for tables based on queries

texts:
I have a db with sensitive data in a secure server, and a set of anonymization queries that I'd like to create foreign tables based on in a less secure server.

The Postgres native wrapper only enables you to create foreign tables directly mapped to tables in the source db, but it seems there are other wrappers which allow you to base the foreign tables based on queries.

Are there any foreign data wrappers for Postgres to Postgres porting that let you do this? Ideally it'd be like so:

create foreign table foreign_table (id integer)
  server foreign_server
  options (query 'some query')


texts:
I have a trigger

create trigger flightinput2 on reservation
after insert
as
begin
update reservation
  set Departure_Airport='LDN',
      Arrival_Airport='KUL'
  where Flight_Code='EM0088'
end

then I have a procedure to modify Flight_Code say from "MH0077" to "EM0088"

create procedure modifyvalue @ID int,@flightcode varchar(6), @date datetime as
begin
update reservation
  set Departure_Time=@date,
      Flight_code=@flightcode
  where ID=@ID

  select * from reservation
end

drop procedure modifyvalue

execute modifyvalue '12','EM0088','2014-08-16'

The result I get made changes to Flight_Code and other necessary data from the procedure but not the departure_airport and arrival_airport as the designed trigger.

I hope I explained my situation clear enough. What did I do wrong, I figured I might not doing this the correct way for the trigger to fire


texts: How to add function to particular column in while creating table in Postgres?

texts: I want to add a function to particular column while creating a table in PostgreSQL. After creating table, it has to generate auto generated id when we insert values into to the table.


texts: Recovering InnoDB database from a full physical backup?

texts:
I had a server running Ubuntu 12.04 crash, and had to fully reinstall. I have a full physical backup of all files stored on the server, including the ibdata1, .frm, and .idb files associated with my databases, which were largely using InnoDB in MariaDB 5.5. However, since this is just a little side project, and an opportunity for me to get my feet wet dealing with server and database administration, I am at a loss for where to proceed from here. I can find numerous references for how to proceed if you are missing any of these files, mostly covering that it is extremely difficult to do so, but possible, however, I cannot find any instructions for how to successfully restore the database from a backup containing all of these files. Clearly I'm missing something.

Moving forward I'll be sure to add making a logical backup to the list of priorities, but it's a bit late for than now. And while I'd very much prefer to not write the data I have off, if that's what it comes to, I'll chalk it up as a learning experience.


texts: MySQL metadata function to get projected column type in query?

texts:
Is it possible to get the field type from a MySQL query, in the same way you can get it from a table with the SHOW COLUMNS command? Such as from a derived table,

SELECT x -- presumedMetaFn(x) -- returns "int"
FROM (
  SELECT 1 AS x
  UNION SELECT 2
) AS t;

Given the above query, is there a function or something that I can use to get the type of x? PostgreSQL makes this available with the System Information Functions pg_typeof

SELECT x, pg_typeof(x)
FROM ( VALUES (1),(2) ) AS t(x);
 x | pg_typeof
---+-----------
 1 | integer
 2 | integer
(2 rows)

I am not looking for metadata on a table, but from the result of query. With psql on PostgreSQL 11+, this is also possible by running \gdesc after the query.


texts: Listing databases gives 'ERROR: invalid byte sequence for encoding “UTF8”: 0xe5 0xc6 0xf5'

texts:
I logged into one of our production databases today and tried listing the databases using \l and got:
ERROR: invalid byte sequence for encoding “UTF8”: 0xe5 0xc6 0xf5

Since I am not touching any content, merely metadata on databases, I am not sure where to begin to attack this. Other than the superfluous similar error, I do not see how answers to similar questions are directly of help. If I were to guess, I would think that one of the names of the databases included chars not representable using utf8.
Clues?
Debugging info
Running Postgresql version 10.4 64bit on Windows Server 2016.
mydb=> SHOW client_encoding;
 client_encoding
-----------------
 WIN1252

mydb=> SHOW server_encoding;
 server_encoding
-----------------
 UTF8

mydb=> SELECT datname::bytea, encoding FROM pg_database;
       datname        | encoding
----------------------+----------
 \x706f737467726573   |        6
 \x74656d706c61746531 |        6
 \x74656d706c61746530 |        6
 \x6e696d626c656462   |        6


texts: Cannot Access Non-System Databases in SQL Server Express 2016

texts:
after I allowed someone access to my SQL Server Express 2016 through team viewer,
I suddenly cannot access non-system databases. No problem with system ones, but I cannot access my other databases. Any ideas?Please see below screenshot

@RDFozz: (Sorry, replies below are disabled) Yes, I knew the user and I am connected as the same user.


texts: Is there a way to create a re-usable base table definition in InfoSphere Data Architect?

texts:
Our company has chose to use IBM's InfoSphere Data Architect (IDA) for data architecting regarding our data warehouse and data marts.

The data architects originally designed several "re-usable" tables for our marts. In other words a table like CUSTOMER_DIMENSION may reside structure-wise in more than one mart. Each mart will have their own copy of their data.

I believe to make things easy, they defined all the tables (dimensions and facts) within the same "schema" that they called DB2ADMIN within IDA.

Since a mart can only have one fact table, I have to look at the diagrams of the table relationships to the fact tables to determine which tables should get generated into a DDL file for a particular mart. As of right now each mart physically resides within its own schema within one database. So not only do I have to manually pick the tables for DDL generation, but I have to manually tweak each DDL file to override the schema. This is not ideal as it is an error-prone process.

Is there a way to have a "base" definition of a table and then inherit it or whatever into the real table within IDA? What I'm looking to do is have schema definitions within the tool same as what we have on our server. I would like only the applicable tables to show up within each schema (as a mart should be - one fact and only its dimension tables it cares about). But to save on work, I'd prefer if the table definitions were in one place. So the architects could make one change, but we could more easily generate our tables.

Is this possible? Are there any gurus with IDA out there?


texts: Tool to find CPU utilization for a particular query in Postgres

texts: I just want to know is there any free tool to identify CPU utilization for a particular query in PostgreSQL.


texts: How to update all primary keys in the existing database

texts:
I'm moving mysql multi-master replication to production using tungsten. I had setup the auto increment and offset values in my.cnf file. To achieve this i'll have to update my all primary keys of all databases to auto increment. Had any one done this? Can some one help me out to do? Current i have 4 databases and i needs to update all primary keys with auto increment.

Thanks, Swaroop


texts: How to deal with unavoidable circular references

texts:
I'll start of with an example database design to give you an idea what I'm currently dealing with.

I have a table which holds all the makes of vehicles and another table which holds the models for each make. This would result in the following db design:

Makes

Id
Name

Models

Id
Name
MakeId

So far it's pretty straight forward, but now comes that somewhat hard part which introduces the 'unavoidable' circular reference in my scenario. I'm introducing another table named Vehicles, which is used to configure a particular vehicle.

Vehicles

Id
MakeId
ModelId
Variant (for example 3.0 TDI, just a plain text field).

The reason I came to this design is because I have a frontend web application which allows the user to fill in a form to configure a Vehicle. Inside the form the user gets presented with two select boxes, one for the make and one for the model. In this case the models belonging to a make are dynamically loaded once a make has been selected by the user, hence the FK relationship between Models and Makes.

Possible solutions:

1) Remove the FK relationships from the Vehicles table and just store the make name and model name, but if for some reason these values change, already inserted Vehicle records won't show the updated name.

2) Store only the ModelId in Vehicles

3) Create a many-to-many table between Vehicles and Makes and Models and store the Id of the many-to-many table as the FK in the Vehicles table. This however would require the frontend to perform a lookup based on MakeId and ModelId to get the id of the many-to-many table.

Which approach is recommended? Are there better alternatives?


texts:
Is there a way to generate mass rows of dummy data in a table for testing in workbench?

If not, are there any free tools out there that is able to do that?


texts: MariaDB why do I get error "Unknown table x in field list" when it does exist (yes, another one!)

texts:
Using mariadb 10.4.13-MariaDB but I get the same error in a copy of the database running under 10.3.
The important part of the schema shows
/*Database: mamlocal*/
----------------------

/*Table Information*/
---------------------

Name                                  Engine  Version  Row_format    Rows  Avg_row_length  Data_length  Max_data_length  Index_length  Data_free  Auto_increment  Create_time          Update_time          Check_time  Collation          Checksum  Create_options  Comment                                                        Max_index_length  Temporary
------------------------------------  ------  -------  ----------  ------  --------------  -----------  ---------------  ------------  ---------  --------------  -------------------  -------------------  ----------  -----------------  --------  --------------  -----------------------------------------------------------  ------------------  -----------
...
member                                InnoDB       10  Dynamic        706             162       114688                0         49152          0            4528  2020-08-17 14:48:55  2021-04-06 10:27:53  (NULL)      utf8_general_ci      (NULL)                                                                                0  N
...

and the table member is created using...
CREATE TABLE member (
  member_id int(11) NOT NULL AUTO_INCREMENT,
  member_title varchar(10) DEFAULT NULL,
  member_fn_1 varchar(50) DEFAULT NULL,
  member_sn varchar(50) DEFAULT NULL,

  ... other fields

PRIMARY KEY (member_id)
)

These statements each produce the expected results.
SELECT * FROM information_schema.tables WHERE table_schema = 'mamlocal' AND table_name = 'member';
SELECT * FROM information_schema.tables WHERE  table_name = 'member';
SELECT member_id FROM member
SELECT member_id FROM mamlocal.member

but these produce Error Code: 1109 Unknown table 'member' in field list
UPDATE member SET  member_id = member_id ;
UPDATE mamlocal.member SET  member_id = member_id

(The real update does something useful but I simplified it to try to find out where the error was)
Can anyone help me to identify why?
I have looked at these other questions but still cannot see what is wrong
Qn1
Qn2
Qn3
Here is an SQLfiddle showing the same code working correctly in MySQL, as I would expect and I have used similar code in many other places without problems to update table member. Using the same code to update another table works ok.
More experiments showing that table member is there.
This code...
INSERT INTO member () VALUES  ();
UPDATE member SET  member_id = member_id ;

...
gives me the message
2 queries executed, 1 success, 1 errors, 0 warnings

Query: insert into member () values ()

1 row(s) affected

Execution Time : 0.002 sec
Transfer Time  : 0.001 sec
Total Time     : 0.004 sec
-----------------------------------------------------------

Query: UPDATE member SET member_id = member_id

Error Code: 1109
Unknown table 'member' in field list

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.001 sec


texts: SQL Server 2016 Always On with Availability Groups: add instance on both servers

texts:
I have a WSFC with SQL Server 2016 Enterprise Edition and 4 Availability Groups. Each server has 1 CPU with 6 cores @ 3.4GHz and 256GB of RAM.

Currently I have one instance on each, they serve 4 Availability Groups with a total of ~300 GB data in 95 databases.

I have the task of migrating more databases (35 databases, 235 GB data) into this environment, which I'm sure would be a breeze - had it not been for the fact that these databases come from a cluster with another collation. Hence, I'm thinking of adding an instance on each server and creating a separate Availability Group for this collation.

To help keep things sorted and sort of simple I will add an IP address on each server for the new instance. That way I can address both instances on port 1433 and won't need to sort that out in the availability Groups Creation - at least that's my thinking.
(I've used this approach on a single server with 3 instances - they all have their own Ip addresses which removes the need to address them by instance name - keeps it easy in case the db needs to migrate to another server. )

I found a similar question from a few years back but since there was no evident reason for the multiple instances the TS received a lot of whys, donts and whats. I my case I really need to have an extra instance due to the collation issue, my question to the Community is: is there anyone who could tell me "Yes" or "No"? And if either answer - could you please give me a good motivation?

If I can't add the instances I would have to go with another server, and due to the license costs of Enterprise Edition this would then be a non-clustered solution - reducing uptime, complicating patch routines, removing redundancy and so on. So: could I do this?


texts:
I found sqlplus'interface is rather outdated. It's quite nice to have some commands or keywords at disposal, but for example no "arrow-up" key for the previous history entry is available.

What is a good replacement / extension for sqlplus? Could be a GUI or better (so it stays useful via SSH) a command line utility.

SQL*Plus is the main command line tool to operate with the Oracle Database.


texts: No IMPLICIT_CONVERSION warning in this query plan

texts:
Take the following minimally complete and verifiable example1 code:

USE tempdb;

DROP TABLE IF EXISTS dbo.t;
GO
CREATE TABLE dbo.t
(
    t_id int NOT NULL
        CONSTRAINT t_pk
        PRIMARY KEY
        CLUSTERED
        IDENTITY(1,1)
    , k sysname NOT NULL
        INDEX t_001
    , s sysname NOT NULL
        INDEX t_002
    , somedata varchar(1000) NOT NULL
        CONSTRAINT t_somedata_df
        DEFAULT REPLICATE('A', 1000)
    , INDEX t_003 (k, s)
);

INSERT INTO dbo.t (s, k)
SELECT sc1.name, sc2.name
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;

CREATE STATISTICS t_st001 ON dbo.t (k) WITH FULLSCAN;
CREATE STATISTICS t_st002 ON dbo.t (s) WITH FULLSCAN;

The following query purposely uses the wrong variable types.  I was hoping the query plan would include an implicit conversion warning, but it doesn't.

DECLARE @k char(128) = 'a';
DECLARE @s char(128) = '';
SELECT s
FROM dbo.t
    LEFT JOIN sys.syscolumns sc ON t.s = sc.name
WHERE dbo.t.s = @s
    OR dbo.t.k = @k;

Why is that?

The query plan is here.

1 - that's my website, BTW


texts:
I am facing this problem when connecting to local system db, but when I connect any server it connects.

Login failed for user 'domain\username'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476


texts: How to create Dynamic table in stored procedure?

texts:
We want call a Stored procedure which returns 4 tables in my Stored procedure.To create dynamic table in my sp I am using OPENROWSET. While executing my new sp i am getting Column name or number of supplied values does not match table definition. exception.

When I debugged my code the dymanic table whic is getting created is having columns for the first table only.

Edit 1:

Create table #temp 'SELECT * FROM OPENROWSET( 'SQLNCLI', 'DRIVER={SQL Server};', 'EXEC spreturninngfourtable')

Insert into #temp 'EXEC
 spreturninngfourtable'


texts: Dropping a constraint (index) on a column

texts:
How can I modify the type on a table that has an index on it? I tried to do an alter column on an empty table to modify the type from date time to varchar(15) and got errors saying that it had dependencies on the column (which turned out to be indexes).

I was able to easily get around this locally by right clicking the index and scripting a drop, but I need to roll this out on other servers where I won’t have access to the index name.

Is there a way I can make a script that will drop any index, let me make that datatype change on the column then read the index? Thanks!


texts: How to reduce the number of things SQL Server puts in the windows event viewer?

texts:
As part of our automated unit tests, a lot of databases get created, set up, then dropped. The problem is that every time this happens, we get a bunch of records from SQL Server in the Windows Event Viewer. The messages are mostly things like: "Starting Up", "Setting database option", and "index restored".

Is there any way to modify the SQL Server config so that it doesn't log these trivial "Information"-level events to the event log. They are filling up the log so that it becomes very difficult to find useful information.


texts: Getting ORA-01031:insufficient privileges during first time login to oracle 11.2.0.1.0 database on my windows 8 PC

texts:
I installed oracle database 11.2.0.1 on my windows 8.1 PC. I downloaded the zip files from oracle website viz. win64_11gR2_database_1of2 and win64_11gR2_database_2of2.

I followed the installation steps and while attempting to login through sysdba, i got the insufficient privileges issue.
I used below command:

sqlplus "/as sysdba"

Please help me to resolve this issue.


texts: How does the optimizer of Firebird determine join order?

texts: As Most query optimizers determine join order via a dynamic programming algorithm pioneered by IBM's System R database project, I was wondering whether the optimizer of Firebird uses the same strategy? And is there any documentation I can refer to?


texts:
I am trying to copy logs from one of my MySQL 5.6 databases to another. Unfortunately, it keeps failing when it tries to insert one of the times with the following error message:

ERROR 1292 (22007): Incorrect datetime value: '2015-03-29 01:01:12' for column 'when' at row 1

To simulate the issue, create a logs table like so:

CREATE TABLE `logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` text NOT NULL,
  `context` longtext NOT NULL,
  `priority` int(1) NOT NULL,
  `when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `priority` (`priority`),
  KEY `when` (`when`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then try to insert a log like so:

INSERT INTO logs (message, context, priority, `when`) VALUES('hello', 'hello', 1, "2015-03-29 01:01:12");

If I change the date in any way, such as make it 2016, or set it to the 30th of March, it will work, but changing the time only sometimes causes it to work. Also, stripping off the time (e.g. "2015-03-29") also appears to work. Is there something wrong with my query?

Version Info

mysql  Ver 14.14 Distrib 5.6.39, for Linux (x86_64) using  EditLine wrapper
OS: Ubuntu 16.04


texts: Why I cannot do this in SSMS directly? (assigning a LOGIN to a USER)

texts:
In SQL Server 2008 R2, I want to change the LOGIN for a USER in a database.

FROM THIS:

TO THIS:

Initially, the database has the [...] dots option to change this greyed-out (why?):

The only way to do this I found, was to do it via T-SQL:

EXEC sp_change_users_login 'Update_One', 'usera', 'loginnew';

Why could I not be able to do this directly in SSMS?

Thanks


texts: Send email when some users connect to SQL Server

texts:
I am looking for a way to create a logon trigger in SQL Server.

Basically i need to do is when it matches some users or profiles on logon it send an alert e-mail.

I have found some things for Oracle but nothing for SQL Server.

Thanks.


texts: SQL Server 2012 Downgraded from Enterprise to Standard. Permission errors

texts:
We downgraded our SQL Server from Enterprise to Standard.

Now on 3 of my 5 non system databases a lot of users no longer have effective permissions. My admin account is in the same boat. I have owners for all the databases. I changed the owner and then changed back but it hasn't helped. Running the service and SQL Server Manager as Administrator didn't either.

On one of the databases no users have any effective permissions?


texts: How can I email query results formatted in html from MSSQL 2000 server?

texts:
The only system SP I have is master.dbo.xp_smtp_sendmail and I can't make it work (or I just don't know how).  I cannot upgrade the DB at this time.  I'm trying something like this:

@body_text=N'SELECT TOP 100 * FROM payhistory WHERE entered BETWEEN
DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)
AND DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4) AND desk=020 ORDER BY entered
DESC

exec master.dbo.xp_smtp_sendmail
@server = 'server',
@from = 'administrator@mydomain.com',
@to = 'me@mydomain.com',
@subject = 'HTML Testing...',
@type = 'text/html',
@message = @body_text

I can do this easily in 2005 and up using "msdb.dbo.sp_send_dbmail" but I am stuck on 2000 due to the size of the DB for now.

Any help or suggestions would be greatly appreciated.

Thanks


texts: Why is MySQL caching data on our dynamic PHP site?

texts:
We are having an annoyance with our PHP/MySQL site in that it seems to cache data from the database and our PHP pages are showing out of date records. In fact, there are pages that link to URLs that have been completely removed from the database, yet they do not show updated records or even missing data.

If we have the patience of waiting several (undefined) hours, the data will eventually show up. I've added the regular no-cache headers via PHP on all of the pages, but it doesn't seem to affect the MySQL output.

The PHP portion of the site is instantly updateable though, and shows HTML etc changes immediately. Is there a way to force MySQL to only draw fresh data from its queries? I thought this was the way it worked anyways!

Thanks,
Chris

P.S. Here are the php codes I'm using to try to force a no cache situation:

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); // Always modified
header("Cache-Control: private, no-store, no-cache, must-revalidate"); // HTTP/1.1
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache"); // HTTP/1.0


texts: inserting a special character into a table

texts: I have a few characters that are being replaced by a ?. How do you keep the original character in the table? The character that I am trying to insert is a ɬ; Latin Small Letter L With Belt. Is it possible to add this character into the table?


texts:
I have encountered the error on SQL Server 2008 R2 where Report Builder 3 fails to load with error

  Microsoft.ReportingServices.ComponentLibrary.Controls.dll, has a different computed hash than specified in manifest.

As described in this SQL Release Services blog post, this problem should be fixed by uninstalling SP3, uninstalling SP2, then re-installing SP3.

However I can't seem to uninstall SP3. When I navigate to Programs and Features -> View Installed Updates, click on SQL Server SP3, and choose Uninstall, I get a wizard window that looks like this:

There is no way to proceed. How do I move forward with this issue so that my users can get back to using Report Builder?


texts: I need to create an alert that will notify me when any query has been blocked for more than 60 seconds.  For example if someone has a transaction open on a table and forgets to run a commit or a rollback.  Is this possible to get from the system tables?


texts: how to prevent UPDATE of existing rows/record but allow INSERT using PostgreSQL trigger?

texts:
How do I prevent UPDATE on any record in a given table, but allow INSERT, I already have created a trigger which prevents update but I found that it also prevents INSERT.
EDIT
I can INSERT new row/record from the database itself but not from the form for a new record of the application that is connected to this database, after submitting the form, the trigger is being executed.
It's a web application built with Django.
TRIGGER FUNCTION
CREATE OR REPLACE FUNCTION prevent_updte()
RETURNS trigger AS
$BODY$
BEGIN
RAISE EXCEPTION 'Data modification not allowed';
END;
$BODY$
LANGUAGE plpgsql

TRIGGER
CREATE Trigger prevent_update
BEFORE UPDATE ON purchases_purchase
FOR EACH ROW
EXECUTE PROCEDURE prevent_update();


texts: Database: what design to have a “document table” referencing others tables

texts:
I have some tables which can have 0..* documents.

How can I design my data model to avoid having a specific document table for each of my tables? Feel free to suggest by editing the data model


texts:
We have a SQL 2016 database that has a 1.9bn row table which has a varbinary(255) column which we use to store the HashBytes of an nvarchar(2000) field in the same table.

We have a Non-Clustered index on the varbinary field and our index maintenance scripts perform a REORGANIZE on this every 2-3 days. But this takes 10+hours to complete.

Any way to improve the speed of index maintenance for varbinary fields?


texts: What exactly is the 'online' in OLAP and OLTP?

texts:
I'm a bit confused because I'm questioning the definition of 'online' in OLTP and OLAP. I used to think that 'online' here means that we want our answer in bounded time and based on the data available at an instance in time.

But OLAP queries can take a some hours to compute - isn't this offline?

And a quick search indicates offline OLAP which sounds rather confusing (offline online analytical processing)...?

What exactly is 'online'?


texts: In Oracle, how to get a list with the names of schemas that a user has read permission?

texts:
In Oracle, i need list which schemas a particular database user (or the current database user) has read permission.

Based on this information, also need to get all the tables and views are in each schema, provided that the particular database user has read permission.


texts: SSRS: http://localhost/ReportServer accessible but no report listed

texts:
I deployed SSRS on Windows 10 in the right way I think, at least, http://localhost/ReportServer is accessible on Internet Explorer.

As you can see in the picture below:

I created a report using SQL Server Data Tools
The report is there, I can also see the preview
The Target Server URL is http://localhost/ReportServer
But if a visit http://localhost/ReportServer there is no report

How is this possible?


texts:
I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK.

The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits
So RN1234567 for example.

However if I strip out the first 2 letters (  right(tbl1.id,7) = right(tbl2.id,7)  )and join only on the digits the query speed improves greatly. Why is this?


texts: Postgresql 9.6 autovacuum blocks all update operations

texts:
I have a table with following definition:

CREATE TABLE public.json_stream_packet_filter
(
  stream_packet_id bigint NOT NULL,
  filter_data jsonb,
  CONSTRAINT json_stream_packet_filter_pkey PRIMARY KEY (stream_packet_id)
)
WITH (
  OIDS=FALSE,
  autovacuum_enabled=true,
  autovacuum_vacuum_threshold=5000,
  toast.autovacuum_vacuum_threshold=5000
);

CREATE INDEX call_started_index
  ON public.json_stream_packet_filter
  USING btree
  (to_text((filter_data -> 'call'::text) ->> 'started_at'::text) COLLATE pg_catalog."default");

CREATE INDEX json_stream_packet_filter_filter_data_idx
  ON public.json_stream_packet_filter
  USING gin
  (filter_data jsonb_path_ops);

It absorbs a lot of updates, and spans with indices to about 6Gb, so every autovacuum takes about two hours. I thought that autovacuum doesn't block either select's, insert or updates, but in fact it is. I have a lot of updates waiting for autovacuum process with page Lock. I look into pg_locks by autovacuums pid and i saw this:

select locktype, mode, relation::regclass from pg_locks
where pid = 3855

relation    RowExclusiveLock    json_stream_packet_filter_filter_data_idx
relation    RowExclusiveLock    call_started_index
relation    RowExclusiveLock    json_stream_packet_filter_pkey
virtualxid  ExclusiveLock
relation    ShareUpdateExclusiveLock    json_stream_packet_filter

so my question is: how i could overcome it? How to tell autovacuum not to lock indice pages, or lock it not for so long time ?


texts:
We're developing a travel/tourist application for a tours company who want to be able to input traveler information into an online database.

Is there any security best practice for storing passport numbers in a database? Should it be encrypted?


texts: Prevent Privileged User from Denying Access to SA

texts:
I have to set up a system where UserA has almost full control over a database (not the server), except for one table that's used by sa, and so sa will always need access to this table.

So far I have:

-- executing as sa
CREATE SCHEMA [sa_schema];
CREATE TABLE [sa_schema].[table1] ( ... );
GRANT SELECT , REFERENCES ON OBJECT::[sa_schema].[table1] TO PUBLIC

CREATE USER [UserA] WITHOUT LOGIN;
GRANT CONTROL TO [UserA] WITH GRANT OPTION;
DENY ALTER , DELETE , INSERT , TAKE OWNERSHIP , VIEW CHANGE TRACKING , UPDATE
  ON SCHEMA::[sa_schema] TO [UserA];
DENY ALTER ON USER::[sa] TO [UserA];

I thought this would be sufficient, but it turns out that it's not. It's perfectly legal for UserA to run:

-- executing as UserA
CREATE ROLE [Denied]
DENY SELECT ON OBJECT::[sa_schema].[table1] TO [Denied]
ALTER ROLE [Denied] ADD MEMBER [sa]

And now sa can no longer read from this table.

-- executing as sa
SELECT * FROM [sa_schema].[table1]

  Msg 229, Level 14, State 5, Line 2

  The SELECT permission was denied on the object 'table1', database 'mydb', schema 'sa_schema'.

Of course, sa could query the database to find out roles it has been added to and remove itself from them, but that seems like a kludge and a lot of work just to perform a select. I think I could avoid this problem by making sure UserA doesn't have the ability to add sa to any database roles or by ensuring UserA cannot modify the permissions on this object, so no matter what roles sa would be added to there's never going to be a DENY on this object.

Does anybody have any ideas on how to make sure sa will always have access to this table regardless of what other changes UserA makes?


texts:
I am testing a MariaDB Galera Cluster set up with only 2 nodes, I have three servers haproxy, node1 and node2 . I was able to set it up and it worked fine until testing phase.
There are two types of test that I made:

The main node fails
The second node fails

While testing I came across this page which describes the problems.
So I was thinking of setting up garbd on both servers as what the guide said, but I don't think it is working(?)
Here is my garbd config:
group="galera-testing"
address="gcomm://10.10.10.16,10.10.10.17"
options="gmcast.listen_addr=tcp://0.0.0.0:4444"
log="/var/log/garbd.log"

If I run sudo garbd --cfg /etc/garbd.cnf the logs stop at: Note: I started this on both servers
INFO: (b363d915-923d, 'tcp://0.0.0.0:4444') turning message relay requesting off

And I can't continue using the terminal without killing it.
Now, I'm thinking of two options:

Setting garbd on my HAProxy Server making it act as the arbitrator or

Setting garbd on both nodes (im confused as the guide says to set it on both servers? what??)

Can anyone point me to the right direction? Thanks!
Note: I was able to test Galera Clusters with three nodes before + HAProxy, and it went better than with two nodes. But to satisfy my curiousity, I kinda want to learn this one works too.


texts: I have an SQL Job which processes minimum amount of rows at a given time. Currently its running every 10 seconds which is the minimum available in Job Scheduler. But because of that the table used processed by the Job gets filled with many records. So I need to run the job every 1 second. How can achieve this? please advice.


texts:
I am working with a MySQL database. I am suppose to combine three select queries, to "improve performance". Each select below is dependent on the previous ID retrieved.

Here is the workflow to get data references between the tables.

There are three tables:

pUserMatch  |  pGroupMatch  |  ProfileData
----------- |  -----------  |  ----------
* username
* GroupID .... GroupID
               ProfileID ....  ID
                               ParentUser

My three select queries...

# get the group ID for the specific username
group_id = select GroupID from Profiles.pUserMatch where username = "<username>";

# now, get the group match ID for the group ID
profile_id = select ProfileID from Profiles.pGroupMatch where GroupID = "<group_id>"

# now, get the profile data for this ID
profile = select * from Profiles.ProfileData where ID = "<profile_id>"

I know a little bit about using joins, but I thought that they were only used when multiple tables have matching data.

So far, I've tried the following...

# multiple select from tables
select usermatch.username, groupmatch.GroupID
  from pUserMatch as usermatch, pGroupMatch as groupmatch
  where usermatch.username = <"username">

# inner join...
select pUserMatch.GroupID, pGroupMatch.GroupID,
  from pUserMatch
    inner join pGroupMatch
      on pUserMatch.GroupID = pGroupMatch.GroupID

I need to select the pGroupMatch.GroupID and ProfileData.ID based on a username param. Is there a way to query this data in a single statement?


texts:
I'm new to MySQL. The GetFamilyTree(id) function that Rolando suggested in the Find highest level of a hierarchical field: with vs without CTEs question was great but when the id is greater than 999 it hangs.

Is there a solution that works with larger ids?


texts: MongoDB error: self signed certificate in certificate chain

texts:
I'm setting up for test a dockerized MongoDB which uses SSL. Everything goes fine with configuration, but when I'm trying to connect I get the following error:

SSL peer certificate validation failed: self signed certificate in certificate chain.

Here's how I generate my CA and server/client certificates:

# CA
openssl genrsa -out ca/ca.key 2048
openssl req -x509 -new -key ca/ca.key -days 10000 -out ca/ca.crt

# Server
openssl genrsa -out server/mongodb.key 2048
openssl req -new -key server/mongodb.key -out server/mongodb.csr
openssl x509 -req -in server/mongodb.csr -CA ca/ca.crt -CAkey ca/ca.key -CAcreateserial -out server/mongodb.crt -days 365
cat server/mongodb.key server/mongodb.crt > server/server.pem

# Client
openssl genrsa -out client/cl.key 2048
openssl req -new -key client/cl.key -out client/cl.csr
openssl x509 -req -in client/cl.csr -CA ca/ca.crt -CAkey ca/ca.key -CAcreateserial -out client/cl.crt -days 365
cat client/cl.key client/cl.crt > client/cl.pem

Here's my MongoDB configuration file (disregard the file paths):

net:
  bindIp: 0.0.0.0
  port: 28018
  ssl:
    CAFile: /etc/mongo/ssl/ca/ca.crt
    PEMKeyFile: /etc/mongo/ssl/server/server.pem
    mode: requireSSL
storage:
  dbPath: /mongo/db
  journal:
  enabled: true

And here's how I'm connecting to it:

mongo --host 127.0.0.1 --port 28018 --ssl --sslCAFile mongoconf/config/ssl/server/server.pem --sslPEMKeyFile mongoconf/config/ssl/client/cl.pem

The local mongoconf/config is mapped to etc/mongo in the container.


texts:
For this question Im really unclear on what happens on step 6. Because at step 5 both T1 and T2 share a S lock on A. And T1 has requested for an X(exclusive) lock and is in waiting because T2 has an S lock. Will the X lock be granted after T2 reads in step 5 or will we encounter a deadlock?
(it is a strict 2pl schedule)


texts: Running query ask me for a Microsoft account

texts:
I log well to a remote SQL Server 2008 R2 with the sa account hosted on a Windows 2008 R2.
I can see all my databases and their belonging tables.
But when I run queries, a popup is displayed asking me for a Microsoft account.
But as I am connecting with sa account I can't use a Microsoft account even a valid one belonging to the remote domain.
When I cancel I have the following error message:

  Unable to acquire authorization token
  (Microsoft.SqlServer.Management.AzureAuthenticationManagement)

What should I do to run remotely my queries with the sa account ?


texts: Import from incremental backups to a new host in Oracle 11g

texts:
I am using Oracle 11g. I would like to know that whether it is possible to import from incremental level 0 & 1 backups to a new host using RMAN. If yes, how can I do that?

For level 1 I am using differential method.


texts:
I have a real world business scenario but for the sake of clarity let me rephrase it as a simpler fictional problem.
We have Buyers and Items.
DECLARE @Buyer TABLE
(
 BuyerId INT IDENTITY(1,1),
 Name NVARCHAR(100),
 Budget INT
);

INSERT @Buyer (Name, Budget) VALUES
('Anna', 100),
('Brett', 50),
('Conor', 20)

DECLARE @Item TABLE
(
 ItemId INT IDENTITY(1,1),
 Cost INT
);

INSERT @Item (Cost) VALUES (50),(30),(20),(40),(10),(40),(30),(10),(5);

The goal is to craft a query (no loops or cursor) which allocates items to buyers, based on their budget.
The most expensive items should be allocated in priority.
Expected result:
ItemId BuyerName
1  Anna
4  Anna
6  Brett
7  NULL
2  NULL
3  Conor
5  Anna
8  Brett
9, NULL

From a logical perpective, this is what happens.
First, the items are sorted by descending cost
ItemId Cost
1   50
4   40
6   40
7   30
2   30
3   20
5   10
8   10
9   5

Then we go through each item and try to allocate it to a buyer that has enough budget left.

Item 1 => Can be assigned to Anna. Anna's remaining budget is 100 - 50 = 50
Item 4 => Can be assigned to Anna. Anna's remaining budget is 50 - 40 = 10
Item 6 => Can be assigned to Brett. Brett's remaining budget is 50 - 40 = 10
Item 7 => No one has enough budget left
Item 2 => No one has enough budget left
Item 3 => Can be assigned to Conor. Conor's remaining budget is 20 - 20 = 0
Item 5 => Can be assigned to Anna. Anna's remaining budget is 10 - 10 = 0
Item 8 => Can be assigned to Brett. Brett's remaining budget is 10 - 10 = 0
Item 9 => No one has enough budget left


texts: How to make a table that could add many same number of different item?

texts:
I wanted to create a table to store coupons of users.

For example, there is 3 coupon which is A, B and C etc. A user can have 10 coupon A, 20 coupon B and 5 coupon C etc.

How should I create a table to store these? There are many users in the system. How should I create a table which able to store all these information?

If I add another table just for the user and the coupon I still need to add a table which can count the number of the coupon. I have 8 type of coupon. Can I just use a single table and store each coupon for each column and row for the number of coupon?


texts:
Hi I create a monthly sale report. there is a week-to-date column at the end of each week, then the end of report, I need to a month-to-date column. The the column will display date and the row will display items. I use SSRS2005 Matrix to do it. I create 8 columns (monday to Sunday, plus WTD) to do it. But I do not have any idea how to solve this MTD column.
Anyone has idea? Thanks in Advance.


texts:
I am using PostgreSQL LISTEN/NOTIFY functionality in Java. So I have made a ListenerBean which is run at Application startup.

LISTENER class in java:

        @Singleton
        @Startup
        NotificationListenerInterface.class)
        public class NotificationListener extends Thread implements NotificationListenerInterface {

            @Resource(mappedName="java:/RESOURCES")
            private DataSource ds;

            @PersistenceContext(unitName = "one")
            EntityManager em;

            Logger logger = Logger.getLogger(NotificationListener.class);

            private Connection Conn;
            private PGConnection pgConnection = null;
            private NotifyRequest notifyRequest = null;

            @PostConstruct
            public void notificationListener() throws Throwable {

                System.out.println("Notification****************");
                try
                {

                    Class.forName("com.impossibl.postgres.jdbc.PGDriver");
                    String url = "jdbc:pgsql://192.xx.xx.126:5432/postgres";

                    Conn = DriverManager.getConnection(url,"postgres","password");
                    this.pgConnection = (PGConnection) Conn;

                    System.out.println("PG CONNECTON: "+ pgConnection);
                    Statement listenStatement = Conn.createStatement();
                    listenStatement.execute("LISTEN notify_channel");
                    listenStatement.close();

                    pgConnection.addNotificationListener(new PGNotificationListener() {

                        @Override
                        public void notification(int processId, String channelName, String payload){

                            System.out.println("*********INSIDE NOTIFICATION*************");

                            System.out.println("Payload: " + jsonPayload);

}

So i make a PGConnection and start listening on the channel.

                listenStatement.execute("LISTEN notify_channel");

So I NOTIFY from the server when a Update of a table is done.

So when an UPDATE http request lands on my server and table is updated, I am not receiving any Notifications on my AS.

I have tested the same LISTEN/NOTIFY on PgAdmin and Notification comes when I run on Pgadmin's console LISTEN notify_channel.

Does LISTEN command goes down ?? How to check whether my Listener is up/down in Postgres ??


texts: How to paste chinese characters in psql terminal?

texts:
I am trying to paste chinese characters into psql terminal:

$ psql
=> \encoding
=> UTF8
# Ctrl+v (艺术与设计)
=> ?????设计

When pasting into normal terminal (Max OS) it works fine:

$ # Ctrl+v (艺术与设计)
$ 艺术与设计

What might be wrong?


texts: Find weighted random row. Or split 1 row into many based on column value

texts:
I want to query the DB and pick a random item weighted by the count. Something like so:

SELECT * FROM (-- SUBQUERY THAT GENERATES WEIGHTED TABLE --)
    ORDER BY ct * RAND() LIMIT 1

I'm thinking the following might be a good approach.  I have a table that looks something like this:

| id  | item   | count |
| --- | ------ | ----- |
| 1   | item a | 3     |
| 2   | item b | 2     |
| 3   | item c | 4     |

How can I query the database so that 1 row is shown per count? I.e. like this:

| id  | item   | count |
| --- | ------ | ----- |
| 1   | item a | 3     |
| 1   | item a | 3     |
| 1   | item a | 3     |
| 2   | item b | 2     |
| 2   | item b | 2     |
| 3   | item c | 4     |
| 3   | item c | 4     |
| 3   | item c | 4     |
| 3   | item c | 4     |

In case it helps, I've created a DB fiddle: https://www.db-fiddle.com/f/wRZgBYkDM18c5fk7tgkBkA/0

I'm using MariaDB 10.0.


texts: AWS VS Azure , which one is preferable for SQL Server 2012 standard version?

texts:
I want few recommendations while considering azure vs aws for our current database which is SQL Server 2012 standard edition.
Currently we are using AWS RDS for managing our database, recently we had been considering active read replica for our heavy prod database, but it seems like aws doesn't have read replica option for MS SQL Server , but only CDC(change data Capture ) option , that doesn't solve our needs because CDC involves manual loading of every other thing except for tables and their primary indexes, whereas for Aurora or mySQL , they have proper read replica options.
Hence we want to know if MS Azure will be better for us in this case.

Thanks in advance!


texts: PostgreSQL: UPDATE trigger on table with OID columns doesn't fire for lo_put-based UPDATEs

texts:
I've created an UPDATE trigger on a table containing an OID column. The intent here is to copy any changes from the OID column to a related BYTEA column.
Example table:
CREATE TABLE blah01 (
    id SERIAL PRIMARY KEY,
    oid_a oid NOT NULL,
    bytea_a bytea
);

CREATE UNIQUE INDEX blah01_pkey ON blah01(id int4_ops);

and trigger:
create or replace function blah01_insert_trigger_fnc ()
returns trigger language plpgsql as $$
begin
    new.bytea_a:= lo_get(new.oid_a);
    return new;
end $$;

create trigger blah01_insert_trigger
before update on blah01
for each row execute procedure blah01_insert_trigger_fnc();

The trigger gets called with UPDATE blah01 ...; statements, and the BYTEA column gets a copy of the large object as expected.
Problem is it never fires when someone invokes lo_put(<some LOID from pg_largeobject also in blah01.oid_a>, ...);.
I fully understand this happens because lo_put is directly modifying pg_largeobject, and there's no real change to blah01.  What I'm after is some reasonable way to have the update to pg_largeobject trigger the update to the blah01 table (and a couple dozen others with similar setups).


texts: Unable to create / Move data to New Partitions from the existing partition SQL Server 2014

texts:
We have an SQL Server 2014 Enterprise Edition and a database around 6 TB in size.

Just to give quick background of infra as Server is in Azure and having premium disk of 9 TB for datafiles.

Have a few large table that is already partitioned by an identifier (MonthlyDate integer Eg 01012016,... 31122016 ). We have monthly partition starting from 2014 to 2016 (01012014 , 01022014......till 31122016).

Now we are trying to create partition for 2017 and 2018 month-wise.

I tried to use the partition wizard, but couldn't find an option for what I wanted. As far as I can tell, I need to run something like:

Alter partition Scheme [PartPScheme_BIGTAB] Next Used [PartFileGrp_201701]

Alter partition Function [PartPFN_BIGTAB] () split range(20170131)

I have tried to run the abrove script multiple time , But it have taken more then 7 hours just only 1 gb of the data has been moved to above partition and finally we have to roll back the script.

I'm looking for someone to point me in the right direction as to best practice for this sort of thing.  Not really sure how to approach this.

What could be the issue?

The current partition function is:

CREATE PARTITION FUNCTION [PartPFN_BIGTAB](int) AS RANGE LEFT FOR VALUES (
      20141031
    , 20141131
    , 20141231
    , 20150131
    , 20150231
    , 20150331
    , 20150431
    , 20150531
    , 20150631
    , 20150731
    , 20150831
    , 20150931
    , 20151031
    , 20151131
    , 20151231
    , 20160131
    , 20160231
    , 20160331
    , 20160431
    , 20160531
    , 20160631
    , 20160731
    , 20160831
    , 20160931
    , 20161031
    , 20161131
    , 20161231
    );


texts: How to set order for constraints to be checked in Postgres

texts:
I have a table with 2 constrains like below.

ALTER TABLE entity_instance ADD CONSTRAINT instance_name_uniq UNIQUE (tenant_id, name)

ALTER TABLE entity_instance ADD CONSTRAINT instance_source_uniq UNIQUE (tenant_id,source_key)

How can I make it mandatory that instance_source_uniq is checked first?


texts: Why would I get PREEMPTIVE_OS_AUTHORIZATIONOPS using tSQLt when Working at Home?

texts:
I have an SSDT project containing tSQLt unit tests.

I always find when working at home that publishing this and running all tests (from a post deploy script) is problematic (against both localdb and SQL Server developer edition).

The publish hangs indefinitely and I eventually have to kill visual studio.

The wait type is PREEMPTIVE_OS_AUTHORIZATIONOPS and an example of a statement hung waiting for this (from sys.dm_exec_sql_text) is

(@r BIT OUTPUT)
SELECT @r = CASE
              WHEN I.Version = I.ClrVersion THEN 1
              ELSE 0
            END
FROM   tSQLt.Info() AS I;

I also reproduce this by calling

SELECT tSQLt.Private::Info()

Which is a simple method

public static SqlString Info()
{
  return (SqlString) Assembly.GetExecutingAssembly().GetName().Version.ToString();
}

I assume that it is trying to contact the domain controller to establish that I have some permissions or other. I don't get this with other CLR assemblies and so suspect that this may be something to do with TSQLT not being a SAFE_ACCESS assembly (permission set is EXTERNAL_ACCESS).

Anyone know what's going on here and how I can fix this and work disconnected from my company's network without encountering this?


texts: Schema for multiple identical queue tables

texts:
I am migrating a message processing application that was built on MSMQ to SQL Server 2012 (preferably Standard Edition).  Messages are received and distributed via TCP/IP endpoints and the typical installation has between 100 and 500 such endpoints.  Each endpoint has its own queue to enforce strict FIFO processing of its messages.  Dynamically creating these queues in MSMQ was no problem.  Dynamically creating individual queue tables in SQL Server isn't really a problem, either, except for the fact that I want the enqueue and dequeue operations to be implemented as stored procedures because the messages are structured and stored in a non-trivial master-detail arrangement.  The separate queue tables would each require a separate pair of enqueue/dequeue stored procedures unless I use dynamic SQL to specify the specific queue table within the stored procedure.

Per Erland Sommarskog's very helpful article on dynamic SQL, the creation of multiple identical queue tables is something of an anti-pattern, but I believe my situation warrants it because storing pointers to all messages in a single queue table would result in significant complexity and fragmentation when maintaining a FIFO ordering on a per-endpoint basis.

Should I just bite the bullet on the dynamic SQL approach to the enqueue/dequeue stored procedures or am I overlooking a simpler solution to the multiple queues problem?


texts:
Can a foreign key in one MySQL database/schema refer to a table in a different database/schema?

If so, what limitations are there on doing this?


texts: How to get information about the sequence behind the new Identity Column in Postgres 10? (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY)

texts:
Postgres 10 brings an implementation of the SQL standard GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY feature, loosely known as identity column. This feature supplants Postgres’ own SERIAL pseudo-type, eliminating its issues/problems. For more info, see here and here.

One of the main reasons for GENERATED… being superior to SERIAL is that the sequence number generator is handled in the background transparently. We can add the column, adjust the current sequence number, and drop the column, all without referring explicitly to the sequence.

CREATE TABLE tbl (
    col  BIGINT
         GENERATED ALWAYS AS IDENTITY
         PRIMARY KEY ,
    col2 text
)
;

And…

ALTER TABLE tbl
ALTER COLUMN col
RESTART WITH 1000
;

And…

ALTER TABLE tbl
ALTER COLUMN col
DROP IDENTITY IF EXISTS
;

Or…

ALTER TABLE tbl
ALTER COLUMN col
DROP COLUMN IF EXISTS
;

Note how in none of that example code did we refer to a specific sequence-generating object.

I presume this feature is implemented using the existing Sequence feature in Postgres.

➠ How do we query this background sequence? How do we ask what the last/next value generated will be? What is the equivalent of the Sequence manipulation functions?


texts: Out of range errors while with insert statements

texts:
I'm having an issue regarding every third insert line of the database I'm trying to create.

Some background, I have a huge .JSON file and used software (sqlizer) to turn it into input statements, however I'm having an issue as you can see here:

Every third statement or so, I run into a range error, that I'm not sure why is happening since I didn't set a range.

Here is a sample from my insert statements:
http://pastebin.com/j7A4ZJV4

Does anyone know what I can do to fix this issue? Keep in mind I'm very new to MySQL so forgive me if I've done something tremendously dumb.

Thank you


texts: Insert data in relation table and into data table with less query cost

texts:
I have a similar table layout:

create table data ( id int identity primary key
                  , value int not null);

create table relation (id int identity primary key
                      , someid int not null
                      , dataid int foreign key references data(id));

When inserting data into the tables, in most of the cases I need to insert something in data and also in relation.

Currently I do it like this:

-- insert data.value = 1337 and relation.someid = 42 + relation.dataid = 1337
if not exists (select id from data where value = 1337)
    insert into data (value)
    values (1337);
    insert into relation (someid, dataid)
    values (42, (select id from data where value = 1337));

Is this the proper solution?

What can I do if I need to insert many of these lines?

Currently I insert all data into data and then do the queries for relation.


texts: Mariadb error 1071: Specified key was too long; max key length is 1000 bytes

texts:
A bit of context: I am trying out flowable, however tomcat chokes at a certain point while populating the database, the usual exception wall of text boils down to the title of this post. First time I put my hands on tomcat in a long time, I may be missing the obvious.
Update: this query now works.
CREATE TABLE flowable.FLW_EVENT_DEPLOYMENT (
ID_ VARCHAR(255) NOT NULL,
NAME_ VARCHAR(255) NULL,
CATEGORY_ VARCHAR(255) NULL,
DEPLOY_TIME_ datetime(3) NULL,
TENANT_ID_ VARCHAR(255) NULL,
PARENT_DEPLOYMENT_ID_ VARCHAR(255) NULL,
CONSTRAINT PK_FLW_EVENT_DEPLOYMENT PRIMARY KEY (ID_)
)

What I did: I switched from utf8mb4 to plain old utf8 thanks to this bit "I know UTF8mb4 can only support up to VARCHAR(191)" (thanks @Lennart for making me think harder about this). So there is progress but I have the same error later on another query:
CREATE UNIQUE INDEX ACT_IDX_EVENT_DEF_UNIQ ON flowable.FLW_EVENT_DEFINITION(KEY_, VERSION_, TENANT_ID_)

Those three columns are respectively varchar(255) utf8_general_ci, int(11), varchar(255) utf8_general_ci.
tomcat 8.5.61
mariadb 10.4.17
I found quite a number of suggested solutions, but none seem to apply to my mariadb version, innodb-large-prefix=ON in particular has been deprecated, tried a few different collations without success. I have spent quite a bit of time on this already, tried to swap mariadb connector for mysql connector too to rule out a bug in the library itself.
For example I tried the accepted solution here to no avail, probably because:

innodb_file_format is deprecated and has no effect. It may be removed in future releases.
innodb_file_format_max surprisingly causes an error on config check, Antelope is deprecated anyway and mariadb wouldn't use it for a new table
innodb_large_prefix was removed in 10.3.1 and restored as a deprecated and unused variable in 10.4.3 for compatibility purposes.


texts:
I have the following data coming from Finger Print devices, Each In and Out of employees are recorded.
UserId         CheckTime              CheckType
------        -----------            -----------
  2      2020-08-03 08:15:12.053          I
  2      2020-08-03 16:00:00.053          O
  2      2020-08-04 08:00:12.053          I
  2      2020-08-04 16:10:00.053          O

I want the above data to be as following:
UserId         CheckTime           CommingTime                 LeavingTime
------        -----------          -----------                -------------
  2           2020-08-03           08:15:12.053               16:00:00.053
  2           2020-08-04           08:00:12.053               16:10:00.053

**Note: ** A user can have multiple In Out record per day
I have tried the following answers but didn't help:
How to merge multiple rows into one row with SQL?
Any idea??


texts:
We are upgrading our infrastructure and for that we are migrating from Oracle 10g to 11g.

We are using Oracle GoldenGate for data replication and it does not support replication of AQ messages as far as we know.

For Persistent queue, we have import/export option available. But there are some Buffered queues as well need to be replicated.

can anyone guide how we can avoid data loss in this situation.

Appreciate any help.


texts: How to get COUNT of a range between 2 columns

texts:
Table:

Staff  | StartingTime |  EndingTime  | starthour   | endhour
-------------------------------------------------------------
10     |     0813     |    1610      |    08       |    16
20     |     0910     |    1224      |    09       |    12
30     |     1132     |    1706      |    11       |    17
40     |     1311     |    2110      |    13       |    21
50     |     1533     |    2230      |    15       |    22

The first 2 time columns are nvarchar(4) and I derived the last 2 columns with LEFT(field, 2) to get the hours.

I need to get a count of the number of staff that were working for each hour. It will need to by dynamic as I will never know the time ranges. Right now I'm just trying to get this working for a single day, but eventually I need it broken out by day.

Expected results:

Hour  |  Staff
------------------
8     |    1
9     |    2
10    |    2
11    |    3
12    |    3
13    |    4
14    |    4
15    |    5
..
19    |    2
etc..

This is on Microsoft SQL Server 2014.


texts: I would like to look at something similar to sys.dm_db_index_usage_stats (This DMV is only for tables with indexes I believe) for the Heap tables? Any idea?


texts: How to write a query to find all tables in a db that have a specific column name

texts:
I've got a database with about 100 tables and I need to build a join query to get specific data from two of them.  I know one but not the other.  Basically I need something like:

select <tables> from <database> where exists table.column name;

How can I do this?


texts: SQL Server 2012 replication setup on routed network

texts:
I'm trying to setup replication between two SQL Server 2012 instances.

I set up a publication successfully on server1, but then I had a problem when trying to access it from server2, because the only way I can reach server1 is using a fully qualified domain name, e.g. server1.mydomain.com. The Netbios name server1 on its own won't work because these two machines are not on the same subnet.

So I got an error saying I should connect to server1.

To try and get around it I renamed server1 to server1.mydomain.com. But now I can't setup replication. I get the error:

  Could not connect to server 'server1' because distributor_admin is not defined as a remote login at the server.

Is there a way to get this working when the two nodes are on different subnets?

Thanks


texts: Intermittent Login Issues with Windows Authentication

texts:
I have a strange one. I've been experiencing occasional login errors via windows authentication for the past couple weeks and the network team and I have yet to find a solution.

I am running SQL 2016 on a physical server running Windows Server 2012 R2.

While this has been happening, I've been telling users to wait five minutes and try to login again. If the user waits five minutes, they will (most times) be able to login. I've had instances where I couldn't log in for 15 minutes, then my next login attempt works.

From the SQL error log, I see entries like this:

  Login failed. The login is from an untrusted domain and cannot be used
  with Windows authentication. [CLIENT: XX.XX.XXX.XXX]   Error: 18452,
  Severity: 14, State: 1.

  SSPI handshake failed with error code 0x80090311, state 14 while
  establishing a connection with integrated security; the connection has
  been closed. Reason: AcceptSecurityContext failed. The Windows error
  code indicates the cause of failure. No authority could be contacted
  for authentication.   [CLIENT: XX.XX.XXX.XXX] Error: 17806, Severity:
  20, State: 14.

From the server, I see this:

  An account failed to log on.

  Subject:  Security ID:        NULL SID    Account Name:       -   Account Domain:     -
    Logon ID:       0x0

  Logon Type:           3

  Account For Which Logon Failed:   Security ID:        NULL SID    Account
  Name:     A User  Account Domain:     A Domain

  Failure Information:  Failure Reason:     An Error occured during Logon.
    Status:         0xC000005E  Sub Status:     0x0

  Process Information:  Caller Process ID:  0x0     Caller Process Name:    -

  Network Information:  Workstation Name:   A Computer  Source Network
  Address:  -   Source Port:        -

  Detailed Authentication Information:  Logon Process:      NtLmSsp
    Authentication Package: NTLM    Transited Services: -   Package Name
  (NTLM only):  -   Key Length:     0

Any ideas what could be causing (sometimes) login failures like this?


texts: How to remove options from mariadb tables?

texts:
Mariadb 10.2.8
I am trying to convert an aria table to InnoDB. My problem is

MariaDB> alter table materiales engine=innodb;
ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'

I searched for this problem and didn't found a solution. Some people sugests using sql-mode = '', but it only changes the error and the conversion can't be done.
It looks like a bug, because the conversion to innodb should ignore this option but I think it could be walked arround by removing the "transactional=1" option.
Then my question is: I can modify options like ROW_FORMAT or TRANSACTIONAL, but how can I completely remove it? (whitout dumpling and re-creating the table because it is BIG)


texts: Validate application to access the database

texts:
I need some help for my issue I will explain.

We have a delphi application named for instance App.exe. We use oracle database with our application.

I would like to allow just my App.exe (my valid application that was built by me) and not other App.exe that some other developer created.

At this moment we are signing our App.exe with a public CA.

So, is there any way to say to oracle to verify the App.exe and allow the connection since it is "official"? Maybe a trigger? Or validating the certificate signed?

Thanks!!

Diego


texts: SQL Server 2017 login language display is wrong

texts:
I have just done a clean install of SQL Server 2017 (CU15) on two of my servers, and have noticed that the displayed language for all my logins shows ARABIC instead of '' or 'English'.

Here is the server settings

Here is a login setting

When I run the following SQL code, I get 'us_english' for the default_language_name:

SELECT type_desc,
        default_database_name,
        default_language_name,
FROM master.sys.server_principals
WHERE name = 'Cerberus'

| type_desc        | default_database_name    | default_language_name |
+------------------+--------------------------+-----------------------+
| WINDOWS_LOGIN    | master                   | us_english            |

Did I mess something up on installation?  How do I correct this?


texts:
I have the following scenario. A users table, a records table and a memberships table.

A user can be associated to zero or more records either directly with the records table

records.user_id = users.id

or via a membership (the membership table contains user_id and record_id)

memberships.user_id = users.id

Let's assume I want to load all the records managed by the user. The query directly translates into

SELECT records.* FROM records WHERE records.user_id = ?
UNION
SELECT records.* FROM records
INNER JOIN memberships ON memberships.record_id = records.id
WHERE memberships.user_id = ?

Is there a way to combine these two queries into a single one without UNION?

I tried starting from:

SELECT records.*
FROM records
LEFT OUTER JOIN memberships ON memberships.record_id = records.id
WHERE records.user_id = ? OR memberships.user_id = ?

But it also returns the records where the JOIN matches for other user memberships. Any hint?


texts: Safe to start Mirroring on Production Server?

texts:
We have a 350 Gig DB on SQL Server 2008. We intend to backup and restore it then immediately start mirroring.

Is starting a mirror a dangerous operation to do on a production database ? Can I expect time-outs and do I need to do this in a maintenance window or is it a relatively safe operation to perform at any time ?

Thanks !


texts: Installing Temporal Tables extension (contrib extension) on windows

texts:
I am trying to install this Temporal Tables extension into my localhost installation of PostgreSQL. (pg9.4, windows 7)

I have figured out I needed to put the files into the 9.4/share/contrib folder (currently the files are at 9.4/share/contrib/temporal_tables-1.0.2) but I cannot figure out how to make CREATE EXTENSION work. I know I have to do the windows equivalent of "make install", and I would like to avoid needing to use Visual Studio if I can.


texts:
I want to list all the partitions created by dynamic triggers in PostgreSQL 9.1.
I was able to generate a count of partitions using this related answer by Frank Heikens.

I have a table foo with an insert trigger that creates foo_1, foo_2 etc. dynamically. The partition for insert is chosen based on the primary key id, a range based partitioning.

Is it possible to display all partitions currently in place for table foo?


texts:
I want to create a configuration with postgresql and pgpool to get height availability (HA). I want to do this:

  .-----.                  .--------.
  |     |           W      |   DB   |
  | APP |-----+----------->| MASTER |----.
  |     |     |            |        |    |
  `-----`     |            `--------`    | STREAMING REPLICATION
              |            .--------.    |
              |     R      |   DB   |    |
              +----------->| SLAVE1 |<---+
              |            |        |    |
              |            `--------`    |
              |            .--------.    |
              |     R      |   DB   |    |
              `----------->| SLAVE2 |<---`
                           |        |
                           `--------`
after failover:

  .-----.                  .--------.
  |     |                  |   DB   |
  | APP |-----+----        | MASTER |
  |     |     |            |  FAIL  |
  `-----`     |            `--------`
              |            .--------.
              |    W/R     |   DB   |
              +----------->| SLAVE1 |---.
              |            |        |   |
              |            `--------`   |
              |            .--------.   | STREAMING REPLICATION
              |     R      |   DB   |   |
              `----------->| SLAVE2 |<--`
                           |        |
                           `--------`

Follow the post for HA PostgreSQL Cluster by Streaming Replication + pgpool-II I am created 4 virtual machines (vm) with vagrant. One Master (M), two Slaves (S1 and S2) and other for pgpool (APP). When I do some queries in APP all work fine. When I stop the master, S1 is promoted to M and the S2 now have a new master. But this get me a problem. I don't know what this error mean and I am lost thinking how to fix it:

2014-08-18 17:04:56 UTC FATAL:  timeline 2 of the primary does not match recovery target timeline 1

I am using:

postgres 9.1
pgpool 3.1.1-1

UPDATE

This error is emitted by S2 when S1 is promoted to Master.

UPDATE 2

Nodes IP Address:

M  - 192.168.1.10
S1 - 192.168.1.11
S2 - 192.168.1.12
APP- 192.168.1.13

This is my recovery.conf file configuration for S1 and S2.

standby_mode = 'on'
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=replicator'
trigger_file = '/tmp/postgresql.trigger.5432'

UPDATE 3

Here is my backends into the /etc/pgpool2/pgpool.conf file:

...
backend_hostname0 = '192.168.1.10'
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/var/lib/postgresql/9.1/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '192.168.1.11'
backend_port1 = 5432
backend_weight1 = 0
backend_data_directory1 = '/var/lib/postgresql/9.1/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = '192.168.1.12'
backend_port2 = 5432
backend_weight2 = 0
backend_data_directory2 = '/var/lib/postgresql/9.1/main'
backend_flag2 = 'ALLOW_TO_FAILOVER'

#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------

failover_command = '/var/lib/postgresql/bin/failover.sh %d %M %m'

And it is failover.sh file:

#!/bin/sh
FALLING_NODE=$1
OLD_MASTER=$2
NEW_MASTER=$3
SLAVE1='slave1'
SLAVE2='slave2'
if test $FALLING_NODE -eq 0
then
ssh -T $SLAVE1 touch /tmp/postgresql.trigger.5432
ssh -T $SLAVE1 "while test ! -f /var/lib/postgresql/9.1/main/recovery.done; $
ssh -T $SLAVE2 "sed -i 's/192.168.1.10/192.168.1.11/' /var/lib/postgresql/9.$
ssh -T $SLAVE2 /etc/init.d/postgresql restart
/usr/sbin/pcp_attach_node 10 localhost 9898 pgpool pgpool 2
fi


texts:
Is it possible to create custom domain / data type and associate some trigger with it, for example hash password?

I will have for example password domain and if I set some value to this column, it will automatically hash that, so I don't have to create trigger for each table with password column


texts: Include all possible values of sub-categories under each category

texts:
How can it be achieved in MS SQL 2016, when I need to always see every state under all existing categories even if there are no records, which satisfy the state formula?

Under a and b below I always want to see all three of later, earlier, and same with null counts whenever there is no corresponding record.

I guess it should be achievable with some sort of cross join?..

declare @t table (tid int not null identity(1,1) primary key clustered
    ,tcat char(1)
    ,tdat1 date
    ,tdat2 date
)

insert into @t select * from (values
     ('a', '2019-01-01', '2019-02-01')
    ,('a', '2019-02-01', '2019-01-01')
    ,('b', '2019-02-01', '2019-01-01')
    ,('b', '2019-01-01', '2019-01-01')
)as t(tcat, tdat1, tdat2)

select
    tcat as category
    ,case
        when tdat1 > tdat2 then 'later'
        when tdat1 < tdat2 then 'earlier'
        when tdat1 = tdat2 then 'same'
    end         as state
    ,count(1)   as howmany
from @t
group by
    tcat
    ,case
        when tdat1 > tdat2 then 'later'
        when tdat1 < tdat2 then 'earlier'
        when tdat1 = tdat2 then 'same'
    end


texts: Deadlock - Due to update causing Index page movement within an index

texts:
I am still troubleshooting deadlock mentioned in this questions.  In that I got help in understanding why a select would hold on to a shared lock and I was able to test and confirm that also.

Now I am looking at second spid involved in the deadlock. That session does an update using a primary key(WHERE BatchId  = @BatchI , BatchId is the PK) and it needs IX on two pages.  Initially I couldn't figure out why it needed locks on two pages of a non clustered index when it was just updating one row.  Then I realized that the update to that row would change the location of the row in the non clustered index and that could be reason why it needs locks on two pages.

Is that correct?

Secondly, please help confirm my understanding of this deadlock is correct.
For simplicity sake lets assume that here are only two pages in that non clustered index.  Page1 and Page2.  So is this what is roughly happening


texts: Windows Server 2019 non-owner node cannot see Cluster Name or IP

texts:
I am trying to build a SQL Server AlwaysOn Availability Group with 2 nodes in Azure. I have created 2 SQL Server VMs and Managed to create a Cluster with both Nodes as members of that cluster.

I have followed the steps in this Article of building the Windows Clusters https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-manually-configure-tutorial
with 1 significant change, I used powershell to create the cluster.

New-Cluster -Name SQLClusterName -Node ("SQLNode1","SQLNode2") –StaticAddress 172.100.10.100 -NoStorage -ManagementPointNetworkType Singleton

Because, if I used Failover Cluster Manager to build the cluster it did not created Cluster Core Resources and I did not gave me the option to select an IP for the cluster.

Anyway, once the cluster has been configured, I tried to configure AlwaysOn Availability Group, but the wizard fails to create Always on Availability Group, complains about it cannot connect to the Cluster.

I went on both Nodes and what I have realised is that I can ping to the Cluster Name or IP and get a response only from the Owner Node. From the cluster Non-owner node I could not ping the cluster name or IP. If I failover the cluster to the 2nd node the I ping behaviour will be contra versa.

Has everyone else experienced this issue? Is so, how you managed to resolve it? Any help or a pointer in the right direction is much appreciated. Thanks in advance.


texts: Count and sum, counted and summed values

texts:
I know the title is a bit mysterious. I just would like to ask how I can optimize this query to work better

I have orders and order_items. Every order_items has order_id from "orders" table so that same order_id can be in various order_items since in and order someone can purchase more than one order_item

I reached my goal with this query. But I think it could have been written better, more optimized.

My goal is to find distinct number of order_ids and total amount of all order_items associated with these order_ids

SELECT
  COUNT(*)       AS total_orders,
  SUM(total_invoice_amount) AS total_amount
FROM
  (
    SELECT
      SUM(invoice_amount) AS total_invoice_amount
    FROM
      order_items a
    GROUP BY
      order_id
  ) b;


texts: SQL Server - How to solve FIFO Cost of OHB... function, cursor,?

texts:
SQL Server 2008R2 - Need to solve FIFO cost of items on hand.
To do this I need to add the cost for the most recent receipts quantity up to the current on hand balance(OHB).

So lets say I have
    Item : b1234
    OHB  : 5

Receipts:
RctNumb | Item  | QTYRct | RctCostPerItem | DATERECD
rct05   | b1234 |      1 |           1.00 | 20160822
rct04   | b1234 |      3 |           2.00 | 20160820
rct03   | b1234 |      3 |           1.25 | 20160819
rct02   | b1234 |      2 |           2.50 | 20160818
rct01   | b1234 |      8 |           2.00 | 20160810

Here I would need:
(1*1.00)+(3*2.00)+(1*1.25) = CostofOHB = 8.25

I have no idea how to address this with SQL.


texts:
I'm working on a DB design for medicines in many countries with different languages. I created a table for the different languages I want and link it to another table of chemicals.  But I find that only one table will contain all medicines for all countries with all languages and the same applies for other tables linked to the language table.  Ths means that the software in any country will contain unneccessary data about other countries and unneccessary overhead on the server.

How can I solve this design problem?

See my question to understand what I mean.


texts: Query optimization for multiple joins postgres

texts:
ER Diagram

Brief description

The database to be used is postgres.

The database might contain upto 2 million AccountHolder records.

Since the AccountHolder table is accessed and updated frequently autovacuum_vacuum_scale_factor for AccountHolder table is set to 0.01

The application only loads latest 5000 records during startup.

As specified in the ER diagram, an account holder record can have multiple addresses,file
attachments,tags and versions(1 to n)

An account holder record is associated with a group and for a given account holder record, there are balance and credit fields (1 to 1)

Since latest records are to be fetched, an index is created on LastModificationTimestamp field of the AccountHolder table.

While joining tables with 1 to 1 relationship INNER JOIN is used.

While joining tables with 1 to n relationship LEFT OUTER JOIN is used.

AccountHolderId in AccountHolder table is the PRIMARY KEY and this is used as foreign key in other tables.

Keeping the above things in mind, I have designed this query to fetch the latest 5000 account holder records as follows.
SELECT *
FROM
     ( SELECT *
       FROM AccountHolder
       ORDER BY LastModificationTimestamp DESC
       LIMIT 5000 ) AS AccountHolderRecord

INNER JOIN
     Groups ON AccountHolderRecord.AccountHolderId = Groups.AccountHolderId
INNER JOIN
      BalanceAndCredit ON AccountHolderRecord.AccountHolderId = BalanceAndCredit.HolderId
LEFT OUTER JOIN
      FileAttachments ON AccountHolderRecord.AccountHolderId = FileAttachments.HolderId
LEFT OUTER JOIN
      Addresses ON AccountHolderRecord.AccountHolderId = Addresses.HolderId
LEFT OUTER JOIN
      RevisionHistory ON AccountHolderRecord.AccountHolderId = RevisionHistory.AccountHolderId
LEFT OUTER JOIN
      Tags ON AccountHolderRecord.AccountHolderId = Tags.HolderId

Question
I am new to Postgres and SQL joins. I want to know whether this query can be further improved. Are there any additional things that I need to take care of to improve the performance?


texts: Is it possible to make thosunds of concurrent transactions even if the max_connections is set to 100 in Postgres?

texts:
This is maybe a dummy queestion but I couldn't understand to have thousands of concurrent transactions in a database even if the max_connections=100.

Definition of max_connections from postgres documentation:

  Determines the maximum number of concurrent connections to the database server. The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start.
  When running a standby server, you must set this parameter to the same or higher value than on the master server. Otherwise, queries will not be allowed in the standby server.
  link

Some DBA's says that we have 5k instant transactions. But actually max_connections parameters is much less than that. (300 for example)

Is there any explanation?


texts:
We have several MySQL backup files of a database in *.SQL format and we want to restore the database by using these files. However, as the sql files contains "DROP TABLE" definitions, running one script cause to remove the data created by the previous script. In addition to these sql files, we have also some other backup folders containing *.FRM and *.MYD files. In all of two cases I think these backups are Differential or Incremental and we have to merge/combine all of them. If so,  how can we combine them without losing data? As I have no experience on MySQL, could you explain a way by using MySQL Workbench if it is possible? Thanks in advance.

Some lines from one of the sql file:

DROP TABLE IF EXISTS `BLACKLIST`;
    CREATE TABLE `BLACKLIST` (
      `ID` int(11) NOT NULL auto_increment,
      `NAME` varchar(30) NOT NULL,
      `SURNAME` varchar(30) NOT NULL,
      PRIMARY KEY  (`ID`),
      UNIQUE KEY `BLACKLISTINDEX` (`NAME`,`SURNAME`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


texts: Is it necessary to connect to a postgresql database in order to create a new server?

texts:
I am writing a script in python 3.7, using psycopg2 to set up a new database from scratch. In all the tutorials I have seen, using psycopg2 starts by connecting to a database and getting a cursor object that can then be used to execute psql commands.

Example:

import psycopg2

conn = psycopg2.connect(database="postgres", user="postgres")
cur = conn.cursor()
cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")

Is it necessary to connect to a database on one server in order to create a new server using psql (and psycopg2) or can you create a server first and then connect to it?


texts:
I have a table "lng_grid_test", which, at the beginning, had around 600 000 000 around. The table is defined as :

-- Table: public.lng_grid_test

-- DROP TABLE public.lng_grid_test;

CREATE TABLE public.lng_grid_test
(
  gid integer NOT NULL DEFAULT nextval('lng_grid_test_gid_seq'::regclass),
  geom geometry(LineString,4326),
  CONSTRAINT lng_grid_test_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.lng_grid_test
  OWNER TO postgres;

-- Index: public.lng_grid_test_idx

-- DROP INDEX public.lng_grid_test_idx;

CREATE INDEX lng_grid_test_idx
  ON public.lng_grid_test
  USING gist
  (geom);

I applied to delete request to this table, reducing the rows to around 490 000 000.
Before processing any further, I thought it would be accurate to execute a vaccum.
So I am currently running a vacuum,

INFO:  exécution du VACUUM sur « public.lng_grid_test »
INFO:  « lng_grid_test » : 3588177 versions de ligne supprimables, 485535819 non supprimables
parmi 5042516 pages
DETAIL:  0 versions de lignes ne peuvent pas encore être supprimées.
CPU 106.20s/230.72u sec elapsed 402.26 sec.

Do you think I should stop the full vacuum process and just chose the vacuum with analyze and freeze options or should I keep running the vacuum? And, if you think I should keep on running the process, what may be the order of time it will take to finish the vacuum?
Because I can't work on the table and I don't have a clue about how many time it will take to finish the vacuum.
Thank you!


texts:
I have 2 documents let's say A ( word document) and b ( pdf version ) stored as blob and full text search enabled.

I have 2 words which are present in both documents. Searching for the first word with the contains operator returns both documents. Search for the second only returns one of them.

I also queried the database to list both search terms and the documents indexed for each ( a sql server system procedure that lists all documents for each search term) and to my suprise both documents show up even with the correct number of occurences in each document.

So why would contains not return all results even if the seem to be in the full text index.

Keyword (binary form) and search_term match exacty for both documents


texts:
What is the path of oratab on solaris?

This is actually a question I google a lot and switching from environment to another, I tend to mix them up and forget.

Today I googled it again and I wanted it in a place where I usually have all the answers.


texts: How can streaming replication be kept lagged by a certain 'delay' time?

texts:
Postgres has had streaming replication since 9.0 and since 9.1 it is possible to pause and resume the application of the streamed WAL records using pg_xlog_replay_pause() and pg_xlog_replay_resume(). As far as I can tell, there is no built-in way of keeping the application of WAL deliberately lagged by a specific time period (eg 24h).

Have I missed a built-in way of achieving this and if not, how should I approach doing it some other way?


texts: How to calculate the cumulative difference between rows in Postgres?


texts: Can I set a filegroup of database offline,change the location of files and bring it online again?

texts: I found that we can only take filegroup offline through t-sql,but to bring that filegroup online we need to restore the whole database.


texts:
I'm trying to create a model for food and it's nutrition facts.

My criteria is:

A food can have multiple servings.
Multiple servings (of a food) can point to the same nutrition facts. (For example: if there are 3 serving sizes for a food, then 2 of them point to the same nutrition facts row, but the third serving size points to a different nutrition facts row.)

Additional information

My thinking why multiple servings can be be linked to the same nutrition facts: On a nutritional label, the serving size can be written like: 2 cookies (50g). I wanted to be able to separate the two sizes (cookies and grams), but have them connected to the same nutritional profile, should they need to be changed.

The second point is where I'm kind of struggling.

This is what I have so far, however, the serving_sizes table seems weird and is perhaps there is a better way. These are my concerns:

I think I want the servings table to have an auto-incrementing ID column because I want to record in another table that I, for example, ate cookies with a serving size of 50 grams. I also realize that size serving_sizes ID would be unique since it is a PK, I could just record the serving size ID, as that uniquely identifies the relationship. (Perhaps that means something is incorrect?) Also, given that this is a many-to-many relationship, is an auto-incrementing ID correct here?
How do I prevent the nutrition_facts_id in serving_sizes from being used by another food_id?

Note: I haven’t discussed this scenario with a domain expert since this is a personal project/exercise. However, I have spent a significant amount of time thinking and doing research about this domain.


texts: Automatic MySQL Sync from master, where Slave was offline for long period of time

texts:
Does anyone here know if there are any tools, not using mysql's built in replication that will sync up two databases when the slave has been inaccessible from the internet for extended periods of time?

The idea is to set up a virtual machine basis for our web developers, who can power on the VM and have it automatically clone from a slave of the production DB for the purposes of testing/development. they all require their own DB's but they should all replicate the master DB on startup.

Diagram of operation:

Production DB  ->  Production Read Only Slave  ->  Dev Virtual Machine


texts: my azure sql database is not connecting with SSMS 2014

texts:
The firewall contain my IP in valid list but I am still getting error in connection. I tried the command

C:\Users\MianAsbat>SQLCMD -U username -P password -S

and got the following response. I am trying from windows 8 virtual machine on mac. Please help. Thanks.

  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server:
  Named Pipes Provider: Could not open a connection to SQL Server [67].
  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server:
  Login timeout expired.
  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server:
  A network-related or in stance-specific error has occurred while establishing
  a connection to SQL Server. Server is not found or not accessible.
  Check if instance name is correct and if SQL Server is configured to allow
  remote connections. For more information see  SQL Server Books Online..


texts:
I have a table called invoice and I have a query that will give me the sum of all invoice amounts for a given account where the invoice has a status of PAID between a certain date range.

Below is the schema and query.

Schema:

CREATE TABLE invoice (
    id             INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    agreement      BOOLEAN NOT NULL DEFAULT 0,
    invoice_number VARCHAR(255) NOT NULL,
    status         VARCHAR(255) NOT NULL INDEX,
    status_text    VARCHAR(255) NOT NULL,
    currency       VARCHAR(255) NOT NULL,
    amount         INT(10) UNSIGNED NOT NULL,
    description    VARCHAR(500) NOT NULL,
    --
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP INDEX,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    --
    PRIMARY KEY (id),
    UNIQUE (invoice_number)
);

CREATE TABLE account_invoice (
    account_id INT(10) UNSIGNED NOT NULL,
    invoice_id INT(10) UNSIGNED NOT NULL,
    --
    INDEX (invoice_id, account_id),
    PRIMARY KEY (account_id, invoice_id),
    FOREIGN KEY (account_id) REFERENCES account (id) ON DELETE CASCADE,
    FOREIGN KEY (invoice_id) REFERENCES invoice (id) ON DELETE CASCADE
);

Query:

SELECT SUM(invoice.amount)
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL
    AND invoice.updated_at >= ?
    AND invoice.updated_at <= ?
    AND invoice.status = "PAID"

Let's assume our date range starts on Jan 01 2018, ends on Jan 02 2018.

Let's also assume I have five invoices:

updated_at is Jan 01 2018, amount is 1000
updated_at is Jan 01 2018, amount is 2000
updated_at is Jan 02 2018, amount is 50
updated_at is Jan 03 2018, amount is 100
updated_at is Jan 04 2018, amount is 100

Given the data above, the query above will give me a result of 3050. Perfect.

Now what I want to do is take the date range, and get a list, as opposed to the SUM(amount) of matching invoices. For each invoice matched against the query, I want to take its date and amount.

As such, the result set should look like this:

[
   {value: 1000, date: "Jan 01 2018"},
   {value: 2000, date: "Jan 01 2018"},
   {value: 50,   date: "Jan 02 2018"}
]

Using that result set, I want to be able to make a second condition to shrink down the result set further based on on an interval of time, e.g. daily, weekly, monthly, yearly.

So if I use a daily interval from the result set above, I should get this:

[
   {value: 3000, date: "Jan 01 2018"},
   {value: 50,   date: "Jan 02 2018"}
]

If I use a weekly interval, I should get this:

[
   {value: 3050, date: "Jan 01 2018"},
]

...and so on. As you see it just essentially does the sum logic of my first query on the result set of my second query.

My current attempt at the first part (no interval logic) is the following query:

SELECT invoice.amount AS value, invoice.updated_at AS date
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL
    AND invoice.updated_at >= ?
    AND invoice.updated_at <= ?
    AND invoice.status = "PAID"
GROUP BY invoice.id

This does not give me the result I'm looking for, so I can't hope to move to the second part with intervals yet. Any guidance would be appreciated.

UPDATE:

SELECT invoice.amount, %s AS "invoice.updated_at"
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL
    AND invoice.updated_at >= ?
    AND invoice.updated_at < ?
    AND invoice.status = "PAID"
GROUP BY invoice.id, invoice.amount, FLOOR(TO_DAYS(invoice.updated_at) - 2) / 7


texts:
We do database import a lot of times per day on a server with those specs :

Xeon E5-1620v2 4c/8t @ 3,8Ghz
32 Go DDR3 ECC 1600MHz
Hard Drive   2x 3 To SATA3
MariaDB 10.14 with XtraDB

Given a 327Mb Database stored on the same server that will do the import, the import will take 3 minutes which is unusually long for this.

My my.cnf :

[mysqld]
character-set-server=latin1
innodb_file_per_table=1
max_allowed_packet=64M
skip-external-locking

innodb_buffer_pool_size=15G
innodb_log_file_size=2G
transaction-isolation=READ-COMMITTED
innodb_autoinc_lock_mode=2

max-connect-errors=100000
max-connections=500

I'm importing the database using the MySQL ansible import module

What could improve import performance without altering the backup database file