texts: Performance of single vs multiple column unique constraint

texts:
I'm using PostgreSQL 9.3. I want to understand if I have an option to make a constraint unique across the entire table vs. unique across a subset of the table (i.e. by using 2 columns in the unique constraint, I restrict the uniqueness), which one is better for lookups?

Consider this table where a unique alphanumeric code is allotted to each student of the class.

CREATE TABLE sc_table (
   name text NOT NULL,
   code text NOT NULL,
   class_id integer NOT NULL,
   CONSTRAINT class_fk FOREIGN KEY (class_id) REFERENCES class (id),
   CONSTRAINT sc_uniq UNIQUE (code)
);

Currently the code is unique across the entire table. However the specification says that it is sufficient for the code to be unique across the class only. For my design requirements there's no restriction either way.

However if I change the constraint to be unique for a given class only, how would it affect lookup by code?

Or, in other words, which of the following combination of constraint & lookup is the best speed wise:

-- 1. unique across entire table, lookup by value
CONSTRAINT sc_uniq UNIQUE (code)
SELECT * FROM sc_table WHERE code='alpha-2-beta'

-- 2. unique across entire table, lookup by value & class
CONSTRAINT sc_uniq UNIQUE (code)
SELECT * FROM sc_table WHERE class_id=1 AND code='alpha-2-beta'

-- 3. unique per class, lookup by value
CONSTRAINT sc_uniq UNIQUE (code, class_id)
SELECT * FROM sc_table WHERE code='alpha-2-beta'

-- 4. unique per class, lookup by value & class
CONSTRAINT sc_uniq UNIQUE (code, class_id)
SELECT * FROM sc_table WHERE class_id=1 AND code='alpha-2-beta'

Question: My understanding is that 2 is better than 1 & 4 is better than 3. But which one's better between 1-vs-3 & 2-vs-4?

Update : Adding output of explain analyze. 3 is bad because there's no index for the lookup. 2 seems to be the best but the table is too small to conclude that.

-- 1
"Index Scan using sc_uniq on sc_table  (cost=0.15..8.17 rows=1 width=72) (actual time=0.041..0.044 rows=1 loops=1)"
"  Index Cond: (code = 'code1'::text)"
"Total runtime: 0.096 ms"

-- 2
"Index Scan using sc_uniq on sc_table  (cost=0.15..8.17 rows=1 width=72) (actual time=0.024..0.026 rows=1 loops=1)"
"  Index Cond: (code = 'code1'::text)"
"  Filter: (class_id = 1)"
"Total runtime: 0.056 ms"

-- 3
"Bitmap Heap Scan on sc_table2  (cost=4.18..12.64 rows=4 width=72) (actual time=0.052..0.053 rows=1 loops=1)"
"  Recheck Cond: (code = 'code1'::text)"
"  ->  Bitmap Index Scan on sc_uniq2  (cost=0.00..4.18 rows=4 width=0) (actual time=0.039..0.039 rows=1 loops=1)"
"        Index Cond: (code = 'code1'::text)"
"Total runtime: 0.121 ms"

-- 4
"Index Scan using sc_uniq2 on sc_table2  (cost=0.15..8.17 rows=1 width=72) (actual time=0.036..0.039 rows=1 loops=1)"
"  Index Cond: ((code = 'code1'::text) AND (class_id = 1))"
"Total runtime: 0.093 ms"


texts: ERROR: operator does not exist: text[] ~~ text

texts:
We have an easy syntax that allows us to look into an array for a single scalar,

SELECT 'foo' = ANY(ARRAY['foo', 'bar', 'baz']);

We can use the same method to match with LIKE

SELECT 'foobar' LIKE ANY(ARRAY['foo%', 'bar%', 'baz%'];

My question is what if you want to do it the other.

SELECT ANY(ARRAY['foobar', 'barbar', 'bazbar']) LIKE 'foo%'
ERROR:  syntax error at or near "ANY"
LINE 1: SELECT ANY(ARRAY['foobar', 'barbar', 'bazbar']) LIKE 'foo%';

I know that syntax doesn't work, but I have expected this to work.

# SELECT ARRAY['foobar', 'barbar', 'bazbar'] LIKE 'foo%';
ERROR:  operator does not exist: text[] ~~ unknown
LINE 1: SELECT ARRAY['foobar', 'barbar', 'bazbar'] LIKE 'foo%';
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I want to filter an array to see if an element exists. Is this possible without using unnest?


texts:
CREATE TABLE "Test" ("ItemQty" DOUBLE NOT NULL );

Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);

Select Sum(ItemQty)
From Test;

Result: 15.400000000000002

Should be 15.4


texts: Maintaining data integrity when one table has multiple relationships, but only one should be used. (is this bad design?)

texts:
I'm working on a database that holds laboratory medical testing orders. The issue I have (maybe) is that I have a table that holds an order number, and a test code. There is also a TestNumber PK.
There are two other tables that have a relationship with this table. One of them holds information for testing urine samples, the other holds information for testing blood samples. Depending on the test code in the first table, an additional record should be inserted into one of the related tables. The problem I have is there is nothing stopping a record being inserted into the wrong table for the test code.

Here's a simplified image of the table diagram showing what I'm talking about:

So if a test code is inserted into the LabOrders table and it's sample type is effectively "Blood," then a record should also be inserted into the table BloodTestDetails. But there is nothing stopping a record from also being inserted (or instead, being inserted) into the UrineTestDetails table... Is there a way I can prevent this and maintain data integrity, or is this a bad design?

If it's a bad design, what kind of recommendation would someone make to hold this type of data?


texts: Inserts through Stored Procedure 6 time slower than direct insert

texts:
We have recently created a stored procedure in order to test the insert speed between a direct query into SQL Server without parametrization (new plan for each different insert) and insert through parametrized stored procedure.

The users are complaining that insert through stored procedure is 6 times slower... How is this possible?

The stored procedure looks like this:

CREATE PROCEDURE [dbo].[spInsertAnalysis]
    @Class varchar(255),
    @Method varchar(100),
    @Name varchar(100),
    @Origin varchar(20),
    @Ultimate varchar(255),
    @Value varchar(100)
AS
BEGIN
    DECLARE @SQLString NVARCHAR(MAX)

    SET @SQLString = 'INSERT INTO [Analysis] ([Class], [Method], [Name], [Origin], [Ultimate], [Value] ) VALUES (''' +
        @Class + ''', ''' +
        @Method + ''', ''' +
        @Name + ''', ''' +
        @Origin + ''', ''' +
        @Ultimate + ''', ' +
        @Value + ') '

    EXEC (@SQLString)
END

I see that SQL has generated 17 different query plans for this insert and I found that the cause is the numeric value is different for each query plan (The rest is the same):

(@0 varchar(8000),@1 varchar(8000),@2 varchar(8000),@3 varchar(8000),@4 varchar(8000),@5 numeric(15,9))
(@0 varchar(8000),@1 varchar(8000),@2 varchar(8000),@3 varchar(8000),@4 varchar(8000),@5 numeric(14,7))
(@0 varchar(8000),@1 varchar(8000),@2 varchar(8000),@3 varchar(8000),@4 varchar(8000),@5 numeric(14,5))

But anyway I don't know why it is getting 6 times slower than making direct inserts...

Any help will be appreciated


texts:
i'm making a MySQL data base , my tables are: users, activitylog, activitytype.

to avoid repetitions i thought about including activitytype in each activity which is logged into activitylog table, for example:

1-Activity Types Table

2-Activity Log Table

Is it an interesting model if yes how could i do it ?


texts:
I have a encountered strange behaviour adding columns to an existing table. I run those scripts using osql several instances.

Let Table a_table have initially three columns A_col, B_col and C_col.

To extend this table I use the following statements:

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
            WHERE COLUMN_NAME = N'D_col' AND TABLE_NAME = N'a_table')
BEGIN
ALTER TABLE [dbo].[a_table] ADD [D_col] int DEFAULT -1 NOT NULL
END

Up to now this script worked fine. I have a script adding two batches of columns (15 each) separated with a GO statement.

IF NOT ... -- col 1
...
END
:
:
IF NOT ... -- col 15
...
END
GO

IF NOT ... -- col 16
...
END
:
:
IF NOT ... -- col 30
...
END
GO

I expect the columns 1-15 to be added first and 16-30 second. But it happens from time to time that the second batch gets executed first so the columns appear in the wrong order (16-30, 1-15).

Any explanation for this behaviour is appreciated!


texts: Set in mySQL a predefined order for SELECT queries without using the ORDER BY clause

texts:
I have a MySQL database and users access it through an old legacy software (OLS) the company bought years ago. OLS connects to MySQL through MySQL ODBC driver. There is no way to change OLS in recent future. The OLS is really slow when browsing records, so the idea is to provide OLS with records in the order users expect them i.e. the last created record, first. I have an auto incremental field, so when I order by desc the records, I get what I need. The problem is that OLS does not allow to manipulate query: the only thing it does is to ask for the whole table (I suppose it internally uses the query "SELECT * FROM xyz;").

I learned here that in SQL there is no "predefined" order if there is no order by clause.

So my question is: how can I tell MySQL "if you get a SELECT statement without ORDER BY clause, then ORDER the table desc using Auto ID field"?

If I can not force MySQL, but I have to act on the ODBC driver, that's is still ok for me.

Oh, by the way, sorry for any error i could have had, is my first question and I tried to stick to the rules.


texts:
Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan.

What is the difference between and index seek and an index scan?

Which performs better?

How does SQL choose one over the other?

I realise this is 3 questions but I think answering the first one will explain the others.


texts: How to execute SQL Server query in SSMS using PowerShell

texts:
I have a script file in my local machine. I have to open SSMS and open the query in SSMS and to execute the query.

For opening the SSMS and query, i have used the following PowerShell script to do it.

ssms.exe 'E:\PowerShell_PROD\Screenshot\ServerDetails.sql' -E -S localhost

How can i run this query using PowerShell and how can i close this query window?

The reason I'm doing this is I have to take the evidence of the SQL Server properties and other information. For that I have a PowerShell script to capture the screenshot.

I'm preparing the PowerShell script that runs every query in SSMS and captures the outcome of that and save into specified path. Now am able to open the query in SSMS and capture the screenshot, but I'm unable to run the query.


texts:
We have a SQL Server 2016 Availability Group with 3 servers.  Twice in the past 5 days we have had performance issues.  8a is our primary server, and 8b and 8c are the secondaries.

We started having issues right before 8PM.  We saw timeouts as well as 502 Bad Gateway errors.  On 8b and 8c we see a huge spike in DPT_ENTRY_LOCK waits as well an increase in LCK_M_IS, LCK_M_S, and HADR_SYNC_COMMIT on the primary at the same time.  Unfortunately we don't know which server of the 3 was timing out because all 3 sit behind a load balancer for read-only connections.

We are currently trying to track down what was locking stuff up but can't figure out what the DPT_ENTRY_LOCK type is on the secondaries.  When SQL Skills doesn't have the answer I start to worry.


texts: How to renew certificates in an availability group without downtime?

texts:
OK, here is the setup. We have two nodes, both running SQL Server 2016, participating in an availability group. Both servers are running on Windows Server 2016 and are NOT joined to an Active Directory domain.
Each server has a certificate, and the other node uses that certificate to connect to it and authenticate.
The certificates need to be replaced since their expiration date is approaching.
It seems like you have to update the certificate on server A, or create a new one, then copy it to server B, then change server B to use that for authentication. However it seems like during the middle of this process, there will be downtime since the "old" certificate will no longer be accepted by server A. This whole process would then need to be repeated again for server B's certificate - creating even more downtime. This seems like a bad situation!
The question is - how to do this without causing server or cluster downtime?
Any help would be greatly appreciated. Also if you have any tips on how to generate the certificates with a 3 year expiration instead of 1 year that will be great. These servers are running on a private network so I think the risk of private key leak is very low.


texts:
I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type:

  The storage requirement for a short string (up to 126 bytes) is 1 byte
  plus the actual string, which includes the space padding in the case
  of character. Longer strings have 4 bytes of overhead instead of 1.

Let's assume that I have a varchar(255) field. And now, the following statements:

If this field holds a string of 10 bytes, then the overhead is 1 byte. So the string will use 11 bytes.
If the field holds string using 140 bytes, then the overhead is 4 bytes. So the string will use 144 bytes.

Are those statements above true? Here someone understands the doc the same way as me but here someone states the overhead is always 4 bytes here?


texts: "merge" two tables -from different databases- together?

texts:
Well say I have a table running on a database at localhost (donor database A), and a receiving table running at another server externally (database B) With table creation (on both sides) like:
CREATE TABLE public.my_table (
    id integer NOT NULL,
    key text NOT NULL UNIQUE,
    description text,
    PRIMARY KEY (id)
);

CREATE SEQUENCE public.my_table_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.my_table ALTER COLUMN id SET DEFAULT nextval('public.language_dictionary_id_seq'::regclass)

Now I wish to merge from database A into database B. However only merging should happen on keys that are not existing on database B, and id should be recalculated (obviously to not have duplicates).
Basically as if each row from database A is insert into database B using:
INSERT INTO public.my_table (key, description)
VALUES (...)
ON CONFLICT DO NOTHING


texts: Multi-Tenant Design with Single DB & Single Schema approach using Sql Server

texts:
How would you design the tables which should isolate the data using TenantId in a single table? I came up with two approaches, written down the has pros and cons with my little experience, but I would like to to ask opinion who have successfully implemented.

Tenant details are stored in Tenant table.
Customer is unique to each tenant and gets stored in Customer table.
Sales Header & Item details are tightly associated to Customer.

Common tables

CREATE TABLE Tenant(
TenantId SMALLINT IDENTITY(1,1) NOT NULL,
TenantName VARCHAR(32) NOT NULL
)

CREATE TABLE Customer(
CustomerId INT IDENTITY(1,1),
TenantId SMALLINT NOT NULL,
CustomerName VARCHAR(32) NOT NULL
)

Approach 1:

Pros:

Save 2 bytes for each record from SalesOrder and SalesOrderItem table by eliminating the TenantId column.

Cons:

To query the Sales Order must always join the Customer table to identify the TenantId.

CREATE TABLE SalesOrder (
SalesOrderId INT IDENTITY(1,1),
CustomerId INT NOT NULL
)
CREATE TABLE SalesOrderItem (
SalesOrderItemId INT IDENTITY(1,1),
SalesOrderId INT NOT NULL
)

Approach 2:

Pros:

Data is nicely segregated by TenantId column in SalesOrder and SalesOrderItem.

Cons:

Waste extra 2 bytes for each record on both tables.

CREATE TABLE SalesOrder (
SalesOrderId INT IDENTITY(1,1),
TenantId SMALLINT NOT NULL,
CustomerId INT NOT NULL
)

CREATE TABLE SalesOrderItem (
SalesOrderItemId INT IDENTITY(1,1),
TenantId SMALLINT NOT NULL,
SalesOrderId INT NOT NULL
)

As you may notice the pros and cons are just reversed for each approach, however I may not be the experienced person to write down the pros and cons in details for each approach. It would be nice, if someone with a similar experience can highlight some things which am totally missing here.

I hope, as the Tenants increase in the future it would be good to create Partitions based on TenantId and move their data into other drives using FileGroup approach in Sql Server.


texts: Collation conflicts between views and functions when using utf8mb4_unicode_ci collation

texts:
Currently running MariaDB 10.3.24 and I really want to fully support the utf8mb4 unicode character set in my databases.
The problem I am running into is that when I try and set everything up to use the utf8mb4 character set and set all collation references to utf8mb4_unicode_ci I then have problems with views that have functions in them .... getting "collation mismatch" error on some but not all comparisons, e.g. this != that or this = that, etc. If I then set all the collation references back to utf8mb4_general_ci then all is fine ..... Is there something that I am doing wrong or is there some sort of work around that I have to do when creating views and functions when using the utf8mb4_unicode_ci collation ....
Thanks


texts: How can I Loop a column value if Row exists using INSERT ON KEY DUPLICATE UPDATE?

texts:
In this Table Scheme

[id  -  name  -  active]
[1   -  alex  -  0     ]

I have a UNIQUE KEY on name, active, What I'm trying to do is to INSERT a new Row

[2   -  max  -   1     ]

using INSERT INTO table (name, active) VALUES (max, 1) ON KEY DUPLICATE active = 0

That I want to loop at active column value,

If the row:[2-max-1] exists with active=1 to change active value to 0

And if exists with active=0 to change it to 1,

And if not exist to INSERT the row with active=1

I keep thinking but can't figure out a way to make this loop using only INSERT . . . ON KEY DUPLICATE UPDATE


texts:
I have an Oracle database 11gr2 and I have installed Oracle Grid on Oracle Linux 5.9. I have two disk groups.

/etc/init.d/oracleasm listdisks :

flash_recovery_area

DATA

The first disk group is mounted on /u01 and the second is mounted on /flash_recovery_area.

The value of fra parameters are:

db_recovery_file_dest = /+flash_recovery_area

db_recovery_file_dest_size = 30G

When I want to connect to rman and get backup from current database, I can but I can not find anything in /flash_recovery_area destination.

Can any one help me?


texts: Can the same database be log-shipping secondary and primary at the same time?

texts:
Here is my scenario:

Database DB1 on Server1 is log shipping primary in data center.

Database DB1 on Server2 is log shipping secondary; Server2 is in remote location. Logs are shipped from data center to remote location via shared virtual Jungle Disk drive accessible both from data center and remote location via internet.

In case I fail over to Server2 I would like to have log backups as well.

So my thinking is after configuring DB1 on Server2 as log shipping secondary I would then also configure it as a log shipping primary (even though these log backups won't get shipped anywhere from Server2). When database DB1 on Server2 is in "secondary" mode log backup job will probably be disabled.

Is this a valid use for log shipping?


texts: How to troubleshoot slow connection issues from SSMS?

texts:
I right click on a server in the Registered Servers pane and select New Query.  Sometimes (like 1 out of 3), it will just hang for 5-15 seconds with the Connect dialog (below).  Other times, it connects immediately.
I thought this may have been a network issue, so I have ping running indefinitely just for comparison and the ping times are steady.  So it's not a network issue.  The servers themselves are not CPU pegged by any stretch of imagination.
This problem occurs with all the SQL corporate boxes that I connect to during the day.
All the SQL Server boxes are v2017, Enterprise edition, running on Windows 2016, which is running in a VMWare VM.  I am using SSMS 17.9.1. I am also running the Redgate SQL Prompt add-on v8.0.7.1917 in SSMS, but (from my testing) it seem to have any bearing on the connection times.
How do I troubleshoot this issue?


texts: What happens when a file size limit is reached?

texts:
I have a SQL Server 2008 R2 Standard Edition database for which I have configured the ROWS (.mdf) database file with autogrowth ON and set to max size to 10GB.

After a while, I get an alert that my tempdb log has filled a drive. I figure that my database's size is stuck on the limit at 9990MB or something, unable to extend any further.

Are those two things possibly related? Would the database, being unable to expand, store its transactions in tempdb?

What happens when a database ROWS file gets filled and someone keeps adding data to it?

The data was coming from SQL Server performance data monitoring:


texts: Who changed the size of the transaction log?

texts: Can I know, who and when changed size of database transation log?


texts: Clustered columnstore indexes and foreign keys

texts:
I am performance tuning a data warehouse using indexes. I am fairly new to SQL Server 2014.Microsoft describes the following:

  "We view the clustered columnstore index as the standard for storing large data warehousing fact tables, and expect it will be used in most data warehousing scenarios. Since the clustered columnstore index is updateable, your workload can perform a large number of insert, update, and delete operations." http://msdn.microsoft.com/en-us/library/gg492088.aspx

However if you read further in the documentation you will find under limitations and restrictions:

  "Cannot have unique constraints, primary key constraints, or foreign key constraints."

This confuses me a lot! It is a good practice (not mandatory) to have foreign keys in the data warehouse for a variety of reasons (data integrity, relations visible for semantic layer ...)

So Microsoft advocates clustered columnstore indexes for data warehouse scenarios; however, it can not handle foreign key relationships?!

Am I correct on this? Which other approaches would you advise? In the past, I have used a nonclustered columnstore index in data warehouse scenarios, with drop and rebuild for data loads. However SQL Server 2014 then adds no real new value for data warehouses??


texts: Are there any good reasons for keeping date and time in separate columns?

texts:
I'm trying to understand our software vendor's decision to keep date and time in separate columns. For example, when the row was created or updated. Both time and date are DateTime columns. We are using SQL Server 2005.

The database holds our ERP system's data and I believe the largest tables contain about ~3 million rows. Most of the tables are roughly between 100 000 - 1 000 000 rows.

I would personally by default choose a single DateTime for a single timestamp. This would allow easier time difference calculations and the date and time parts could be easily extracted from the timestamp. It would also consume less space.

Is separating date and time bad practice or is there something very brilliant in this design I don't understand?


texts: SQL Server: hierarchy of permissions for schema?

texts:
We have several databases under development and I would like to start using SQL Server schemas for permission management and logical grouping of database objects. Under Schema Properties there is explicit permissions like

Alter
Control
Delete
Execute
...

What is the hierarchy of these permissions? The idea is to grant Control for admin AD group, and give developer AD group some rights that allow them to do their work (ddl, read, write). What rights should be given for developers?


texts: Check all tables in a schema for X fields?

texts: I want to make sure that all of the tables in my schema have fields named "updateddate" and "createddate". Obviously, I could check them manually, but how can I generate a list of tables that are missing these fields? Even better, how can I use the resulting list and generate the SQL needed to create the missing column for that table?


texts: SQL Server log shipping: How to give SQL Agent the needed rights to remote share?

texts:
This is an old topic, and somehow I have not been able to find a clear answer.

Setup: Two stand alone SQL Servers. They have full network visibility to each other. They are NOT in a domain. There is NO domain available. (I am using SQL Server 2014, but this exact issue applies at least back to SQL 2005.)

Everything "works" ... the only piece missing is that the sql job that tries to copy the log backup fails with this error:

Error: Cannot open backup device '\\sql2\sqllogs\NORTHWND_20150209053800.trn'.
Operating system error 5(Access is denied.).

SQL Agent is running as the "NT Service\SQLSERVERAGENT" account. On SQL2, I have given this user full rights to \sql2\sqllogs\ share and folder.

The SQLSERVERAGENT account on both systems has the same password, and the agent has been restarted after setting that password.

I have seen some folks solve this by making SQLSERVERAGENT a member of the administrators group on the OS, but that does not sound like a safe path.

What is the correct way to solve this?


texts: Incorrect data when we using the CONVERT_TZ  function on DST boundaries


texts:
We've got a process that's importing a 7gig / 334 million row data file. The data file is sorted on the first column

The process is built on the "import first, index later" methodology.  It uses BCP.

As written, it takes about 23 minutes to load the data, and about 45 minutes to create 2 indexes on the 334 million rows.

I tried the following things to speed things up:
Add TABLOCK hint to bcp - cuts the load to 15 minutes
Use BULK INSERT w/ TABLOCK hint - cuts the load to 10 minutes.

All the above are with a batch size of 5000

The thing that puzzled me is that I tried to leverage the sorted order of the input to get some of the work done up front.  I added a clustered primary key on the file sort order and added an ORDER() hint on both the bcp and BULK INSERT loads - and I got only minimal to no gains.

When I was building that index separately it was about 22.5 minutes.

Adding the ORDER() hint with a clustered primary key declared at the beginning just moved that 22.5 minutes to the load instead of an after step.

Shouldn't the sorted nature of the file have produced some more efficiencies?

EDIT: I added trace flag 610 the BULK INSERT statement after noticing the line in the performance guide about BATCHSIZE != 0 would only be minimally logged for the first batch.

Loading with a clustered primary key to an empty table with BATCH=5000,TABLOCK,ORDER(1) and trace flag 610 took 19 minutes, compared to (10 + 22.5) for loading and indexing separately.

So that was a nice improvement but not "similar performance" as the performance guide indicated.


texts: How to set up development servers SQL Server

texts:
My .NET development team has been massively restructured recently, and as a result of the restructure, we now have the ability to do things like set up source control and database backups, and development databases.  However, none of us is really a DBA, and so we have no clue how to do this on the DB side.

We would like to have some way to make it so that developers can be working with real data for testing, but are unable to change the data on the production server.  Right now, we just have one database, so if we need to test a change to the website, we have to make dummy records to test.

Basically, we know this is a bad setup, but we don't know any better, and we really want to change.  I can't find anything online that gives an overview of how to actually set up a development environment which can be used for both application development and database development.

I've looked at How to develop a database (workflow)? and How to setup local database development process for small web team?, but the first one doesn't address my problem, and the second one assumes a lot of knowledge that I don't have. I don't know anything about "normal" database development practices, rollout/rollback scripts, how to write tests for database development, schema comparison tools, etc. (These are all terms I've seen in my search, but I don't know where to go to learn about them.)

Does anyone know of a place where I can go to learn about this stuff?  And/or can anyone give me some advice on how to set up the environment that I don't know how to describe?

My team is very averse to using TFS.  I'm not sure why (I've never used it myself), but they like Git, and I know Git, so that is the source control we've decided on.  My main question was actually how to set up separate servers for the different environments, while having current data to work with in development.


texts: How to add additional hard drive(s) to existing database

texts:
I have a database that is close to filling the hard drive that it lives on.  I'd like to expand the database on to another hard drive to deal with this.

I see there's a Files option in the database properties under SSMS that I could add to but I don't really want to go adding files without knowing what comes next.  Do I need to add a new entry to Filegroups and then add to Files?  Is the PRIMARY type only supposed to be used for the first file?  If not then what is the difference between making one primary and another secondary?  Once I add a file, does the DB engine automatically decide where to put data or do I have to go into my tables and specify this in some way?

Sorry if this is a duplicate.  I'd be surprised if it isn't but I couldn't find another question that had these answers.


texts: MSSQL Spatial performance (point in polygon)

texts:
Discolsure: This is a cross post from GIS, however the group over there isn't necessarily focused

I have a query, below, that works - however its very slow, looping through roughly 24,000 points per second. This is a rather high end production DB server with MSSQL and SDE, and in other instances a 10,000,000 point shape can be loaded in to view in ArcMap in ~5 seconds.

The question is: Can the SQL query be structured to perform any better? My polygon is being given as points, and being converted to geography. My data has a Shape column that according to MSSQL is stored as geography. In order to get the query to work I'm asking every field to be converted via STAsText - and I think that's a performance hangup. For the life of me I cannot seem to get the query to work any other way. If I remove this, the query fails to run.

Ultimately a similar query will be run on a table with 20,000,00 points, so the performance as it is currently is not really acceptable (That would be 833 seconds).

The code as it is now:

DECLARE @geog1 geography;
SET @geog1 = (geography::STPolyFromText('POLYGON((153.013377 -27.385831, 153.018303 -27.399108, 153.030438 -27.395896, 153.028725 -27.386901, 153.013377  -27.385831))', 4326));

SELECT * FROM [GISDB].[GISUSER].[POINTS]
WHERE @geog1.STIntersects([POINTS].[Shape].STAsText()) = 1;

.

The data in the shape field is stored like such (Well Known Binary, I'm guessing)

SELECT [Shape] from [GISDB].[GISUSER].[POINTS]
WHERE [OBJECTID] = '100000';

    Returns: 0xBB100000010C300A689B1B4B3BC0400E645B061F6340


texts: Postgresql: How to avoid encoding issues when copying a schema from one server to another?

texts:
I'm using pg_dump and pg_restore to move a schema from one Postgresql 9.5 server to another. On the destination server:
$ pg_dump -h source.example.com -n my_schema -v --no-owner -F c -f my_schema.dump
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = (unset)
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
...
pg_dump: saving encoding = UTF8
(dump completes with no other errors or warnings)

$ pg_restore -h 127.0.0.1 -e -v --no-owner -d my_db my_schema.dump
...
perl: warning: Falling back to the standard locale ("C").
...
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2211; 0 6549333 TABLE DATA mention chicken
pg_restore: [archiver (db)] COPY failed for table "mention": ERROR:  invalid byte sequence for encoding "UTF8": 0xcd 0x2e

Any idea on how to solve this issue? What I want is an exact binary copy of the data. There seems to be some encoding problem which makes me nervous, since what is restored may not be exactly the same as the dump, even if I don't get any errors.


texts: How to query efficiently from Postgres to select special words?

texts:
Let's say that I have a table called words with very many records.
Columns are id and name.

In the words table I have for example:

 'systematic', 'سلام','gear','synthesis','mysterious', etc.

NB: we have utf8 words, too.
How to query efficiently to see which words include letters 's', 'm' and 'e' (all of them)?

The output would be:

systematic,mysterious

I have no idea how to do such a thing. It should be efficient because our server would suffer otherwise.


texts:
I have created a Database Project in Visual Studio 2010 for a database which is in 2008.
I am having issues in deploying the database as I don't know why, but at the time of deployment in some procedures

QUOTED_IDENTIFIER is set to OFF

But I intended to switch in ON at the time of deployment.

Is there is any setting from which I can control QUOTED_IDENTIFIER settings in my database project?.
Is there any place within database project from where i can switch it on by default at the time of creation or altering..?


texts: Database on secondary replica cannot join availability group

texts:
I am trying to add a database to an Availability Group Always On. I already have the other two DBs working fine on the same SQL Server 2016 Standard Edition Cluster (running on Windows 2016 Standard).

When joining the secondary DB to the AG the database ends up in Not Synchronizing / Recovery pending state. I followed different online sources with alternative solutions without any success.

Has anyone else experienced a similar situation? Any suggestions?


texts: Assistance to understand the Microsoft's Inventory Database model

texts:
checking the Database Model of the Northwind Database I could not understand the relationships of the [Inventory Transactions].
Mainly this table will store In and out transactions based on [Purchase Order ID] and [Customer Order ID].
I'm lost regarding the transaction of the product itself. I mean, there is a relationship with the Products table by [Product ID]. In my understanding it will work to manage the product OUTPUTS.
So why the table Purchase Order Details has a relationship with Inventory Transaction table by its [Inventory ID] ? If it is correct, why the Order Details table is not following the same concept even with the fields available in the table?
If someone can explain this model. Or perhaps have a better approach.
thanks in advance.


texts:
Maybe for this community my problem is easy, but for me (a simple Java programmer) it's a BIG problem.

I have a Big DB with more and more data. So, the external db admin had create a job that show me in a temporary table the data that I need. But he had create the table without a primary key and when with my java project go to read this table, I obtain an error.

I can't read this table because the primary key don't exist.

Can I insert in the procedure the possibility to create an autoincremental primary key without changing the structure of this complex procedure?

This is the begin of the stored procedure code:

USE [MYDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[spSchedula_Scadenzario]
as
begin

    drop table MYDB.dbo.tmpTable

 select
  aa.*
    into MYDB.dbo.tmpTable
from (...)

Thanks in advance


texts:
I need to export the data and structure of a table but the data must have a specific condition (WHERE status=0 and id>20).

How to export mysql database based on a where condition from phpMyAdmin or anything.


texts: Is it possible to backup a database that is not ONLINE?

texts: We have a job which backs up all ONLINE databases nightly, but for some reason it didn't back up msdb. So the issue is at that time msdb was in some status was other then 0. So my question is : is it possible to back up a database if its status is not online (either RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT, EMERGENCY)?


texts:
I am running PostgreSQL 9.3 on my machine. I have PhpPgAdmin running on a cloud, using which I exported the database I am supposed to be working on, selecting "Structure and Data" and "Download". There is an sql file generated. On my machine however, I open my PgAdmin3 client , initialize my databases and run the following import command

psql -U postgres -d postgres -f dump.sql

Nothing happens. I also tried

psql postgres < /path/to/dump.sql

But again nothing happens. I tried on both Windows as well as Linux Mint because I thought it was something wrong with my installation on Windows.
I am new to PostgreSQL and the equivalent for it in MySQL using PhpMyAdmin was simple. Am I missing any security things? I think not because just so that nothing gets confused, I saved all my names as postgres itself.

I have researched everywhere but nothing so far. Any help would be appreciated. Thanks!


texts:
I updated mysql recently and found problem related to privileges and backup. When I am using mysql_upgrade command it says-

mysqlcheck: Got error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`'`  FOR UPGRADE' at line 1 when executing 'CHECK TABLE ...  FOR UPGRADE'

I searched this problem and found that this problem is due to use of delimeter in table_name and If I alter the name of table then this can be solved.
But I don't know how to search those table which are not ready to upgrade ?


texts: MySQL Workbench - Searching for column in query result

texts:
I'm using MySQL Workbench and I have a query result with hundreds of columns within it.
Is there a way to search/go to a specific column within the query result (Using the MySQL Workbench interface)?
Thanks,
Moty


texts: postgresql: procedure to return the number of occurrences of given year and service

texts:
I am creating a procedure to return the number of times a given service is done in a year. The procedure cannot return a NULL value. The table I am taking the data from is called "tb_orders" and has "created_dt" and "order_code" as columns; for the datetime the service was provided and the code of the service itself.
So far I've got this:
CREATE FUNCTION catalog_yearly_orders
  (year, order_code) RETURNS numeric AS $$
DECLARE
  n_service integer NOT NULL,
  year YEAR(tb_orders.created_dt)%TYPE;
BEGIN
  SELECT order_code INTO order_code
  FROM tb_orders
  WHERE order_code = order_code;
  n_service = n_service + 1;
  RETURN n_service;
END;
$$ LANGUAGE plpgsql;

I think what I am missing is some command, between BEGIN-END, for the parameters to match the tuples I am looking for so that I get the ones which coincide with year and service code.


texts: Default value of a column based on other column in MySQL

texts:
I have a mytable table in my MySQL database.

It contains the following columns:

id (INT) PRIMARY KEY - AUTO INCREMENT - DEFAULT NONE
name (VARCHAR (220)) - DEFAULT NULL
code (VARCHAR (220)) - DEFAULT NULL

Is it possible that if I insert something in this table I want the code column to autogenerate its value based on the columns id and name.

If I tried to run this example query:

INSERT INTO mytable(name) VALUES ('myname');

I want to get the following result:

+----+--------+-------+
| id | name   | code  |
+----+--------+-------+
| 1  | myname | my1   |
+----+--------+-------+

The code column default value must be the first 2 characters of myname which is my plus the id column.

I can actually do this using PHP code but I think there must be a way using MySQL only.


texts: Is there a better way than the ones listed within to join data in an excel spreadsheet to a table in a SQL Server database?

texts:
Let's say I'm given a list of names and email addresses in an excel spreadsheet. I am asked to find all customers in the database who's name, and email are in that list.

I currently know of two ways to achieve this...

Import the data into a new table (to be removed later) and then use this table to get the data.
Paste the names and addresses into a SQL query.

Is there a 'better' way to do this kind of thing (i.e. one that doesn't require me to place the data in the database before using it, and doesn't require me to create a monster query that has all the data in a 'where x in (data)' part)


texts:
Does the Microsoft SQL Server 2014 In-Memory Engine supports Shared Memory access like Oracle TimesTen? I only found that there was a protocol with this name in SqlServer2008  but i found nothing for SqlServer2014

thanks


texts: How can I get the latest five scores per user per subject?

texts:
There is a Postgres table in our system called "scores" which records test scores. It has test takers (users of our system), scores from 0-10, depts and dates each time a test was taken, like this:

id  |  user_id  |  score  |  dept      | cdatetime
---------------------------------------------------------
1   |  123      |  5.5    | math       | 2014-01-01 12:00
2   |  123      |  8.9    | chemistry  | 2014-02-01 03:20
3   |  123      |  0.2    | physics    | 2014-01-01 09:00
4   |  123      |  1.4    | math       | 2014-03-01 12:00
5   |  456      |  9      | biology    | 2014-01-18 05:20
...

A user can have more than one score per dept.

What I want is: If a person took more than 4 tests in a dept, the average of the last 5 scores per user per dept.

I feel I'm almost there with this query:

SELECT user_id, round(avg(score)::numeric, 2) AS sc_avg, dept
FROM  (
   SELECT *
         ,row_number() OVER (PARTITION BY user_id, dept ORDER BY cdatetime DESC) AS rn
  FROM mg.scores
  WHERE score IS NOT NULL) as score
   ) AS x
WHERE  x.rn <= 5
GROUP  BY user_id,dept;

However, the depts with less than 5 scores show up :(. There must be something that is wrong in the query to do with window functions, I can't spot it though...

Is there a better way to write this query?


texts: SSIS flat file manager - skips the last record

texts:
I've a a flat file which is pipe delimited as shown below:

header|header2
abc|abcdef
abc|abcdef

When I create a package on a 64 bit machine it always drops the last record. But on preview it shows both records.

Update:

If I do this it works:

header|header2
    abc|abcdef
    abc|abcdef
       |

Why is this and how do I fix it?


texts: Copy complete structure of a table in MySQL without using a backup?

texts:
In MySQL, how can someone copy a table structure without using a backup?

       jcho360> show create table actor\G
*************************** 1. row ***************************
           Table: actor
    Create Table: CREATE TABLE `actor` (
      `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
      `first_name` varchar(45) NOT NULL,
      `last_name` varchar(45) NOT NULL,
      `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`actor_id`),
      KEY `idx_actor_last_name` (`last_name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    jcho360> create table actor_copy select * from actor;
    Query OK, 200 rows affected (0.07 sec)
    Records: 200  Duplicates: 0  Warnings: 0

 jcho360> show create table actor_copy\G
    *************************** 1. row ***************************
           Table: actor_copy
    Create Table: CREATE TABLE `actor_copy` (
      `actor_id` smallint(5) unsigned NOT NULL DEFAULT '0',
      `first_name` varchar(45) CHARACTER SET utf8 NOT NULL,
      `last_name` varchar(45) CHARACTER SET utf8 NOT NULL,
      `last_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)

when I do something like that I lose the structure, like PK, FK, index , ETC.


texts: Incorrect Syntax Near `=` - SQL Expression

texts:
I'm trying to include a conditional check in a WHERE statement.But i keep getting Invalid expression at = 3
WHERE APCCL.data = @coolval
          AND CCMF.Status_ID = 1
          AND APCCL.Status_ID = 1
          AND CLM.myval = 3
          AND
          CASE APEL.numval
          WHEN 1 THEN  CLM.value =  3 END

Basically i want to check CLM.VALUE=3 when APEL.numval=1 otherwise this AND Condition should not be executed.


texts: How to replicate and sync MongoDB localhost to a remote server using replica set

texts:
Initially I started two node(primary and secondary) on same host machine on two different port , secondary was secondary was correctly replicating primary , but my actual requirement is on two different machine with two diff IP address , one should act as Primary other one should act as secondary ,To do that I remove secondary in my local host , and see status below , then tried to add remote IP on same replica , But showing error described below ,

"members" : [
            {
                "_id" : 0,
                "name" : "localhost:27017",
                "health" : 1,
                "state" : 1,
                "stateStr" : "PRIMARY",
                "uptime" : 5359,
                "optime" : {
                    "ts" : Timestamp(1502370582, 1),
                    "t" : NumberLong(1)
                },
                "optimeDate" : ISODate("2017-08-10T13:09:42Z"),
                "electionTime" : Timestamp(1502366671, 1),
                "electionDate" : ISODate("2017-08-10T12:04:31Z"),
                "configVersion" : 4,
                "self" : true
            }
        ],
        "ok" : 1
    }

myreplset:PRIMARY> config = {_id:"myreplset", members:[          {_id:0, host:"localhost:27017"},          {_id:1, host:"192.168.205.59:27017"} ]          };
    {
        "_id" : "myreplset",
        "members" : [
            {
                "_id" : 0,
                "host" : "localhost:27017"
            },
            {
                "_id" : 1,
                "host" : "192.168.205.59:27017"
            }
        ]
    }
    myreplset:PRIMARY> rs.initiate(config);
    {
        "info" : "try querying local.system.replset to see current configuration",
        "ok" : 0,
        "errmsg" : "already initialized",
        "code" : 23,
        "codeName" : "AlreadyInitialized"
    }

myreplset:PRIMARY> rs.add("192.168.205.59:27017");
{ "ok" : 0,
 "errmsg" : "Either all host names in a replica set configuration must be localhost references, or none must be; found 1 out of 2",
"code" : 103, "codeName" : "NewReplicaSetConfigurationIncompatible"
 }


texts: Difference of sets with different amount of columns?

texts:
Problem

When there exists two sets, R and S, and we take the difference R - S, we remove the elements from R that exist in S.

For cases where the number of columns are the same on both sides, such as the example below, this works fine:

Question

However, what happens when R and S have a different amount of columns?


texts: Can't restore MySQL DB because of a function

texts:
I'm trying to restore a database dump that I took via mysqldump onto a new server.  Here's how I made the dump:

 mysqldump --routines --triggers database_name | pbzip2 > database_name.sql.bz2

This works fine & I transfer the dump over to the new server to restore it.  I should mention the database also contains two functions:

mysql> show function status;
+------------------+--------------+----------+--------------------+---------------------+---------------------+---------------+---------+
| Db               | Name         | Type     | Definer            | Modified            | Created             | Security_type | Comment |
+------------------+--------------+----------+--------------------+---------------------+---------------------+---------------+---------+
| database_name    | function_1   | FUNCTION | analytic@localhost | 2016-05-13 00:56:26 | 2016-05-13 00:56:26 | DEFINER       |         |
| database_name    | function_2   | FUNCTION | analytic@localhost | 2017-07-18 20:47:59 | 2017-07-18 20:47:59 | DEFINER       |         |
+------------------+--------------+----------+--------------------+---------------------+---------------------+---------------+---------+
2 rows in set (0.00 sec)

I created the user and gave the same grants for the analytic user on the new DB server that existed on the old server, then I proceed to restore the database:

GRANT EXECUTE ON FUNCTION `database_name`.`function_1` TO 'analytic'@'localhost';
GRANT EXECUTE ON FUNCTION `database_name`.`function_2` TO 'analytic'@'localhost';

However, restoring the database results in this error every time:

ERROR 1370 (42000) at line 16767: execute command denied to user 'analytic'@'localhost' for routine 'database_name.function_2'

I'm assuming it's because it's dropping the table during the restore, which also drops the grant for the user?  I assume this because the grant I create for this user is no longer there after I attempt the restore.

I even went so far as to grant all privileges to this user on that DB, but I got the same error.  What is the right way to restore this database successfully?

The dump came from a very old server running MySQL 5.0.95, and I am restoring it to a server running MariaDB 10.1.14.


texts: Scriptable command line access to SQL-Server from linux

texts:
Does anyone know of a ready made1 command line tool that would allow me to connect from a linux client to a sql-server?

I want to be able to run arbitrary queries, but most of the time I want to be able to take a database dump and then restore it.

This has to be scriptable as it will integrate in an automated build environment.

1FYI: My only other alternative is to write something. I'll probably use perl and DBI


texts: MySQL Unions/Subselects not utilizing keys from associated tables

texts:
I've noticed by doing EXPLAINs that when a MySQL union between two tables is used, mysql creates a temporary table, but the temp table does not use keys, so queries are slowed considerably.

Here is an example:

SELECT * FROM (
    SELECT
    `part_number`,
    `part_manufacturer_clean`,
    `part_number_clean`,
    `part_heci`,
    `part_manufacturer`,
    `part_description`
    FROM `new_products` AS `a`
UNION
    SELECT
    `part` as `part_number`,
    `manulower` as `part_manufacturer_clean`,
    `partdeluxe` as `part_number_clean`,
    `heci` as `part_heci`,
    `manu` as `part_manufacturer`,
    `description` as `part_description`
    FROM `warehouse` AS `b`
) AS `c`

WHERE `part_manufacturer_clean` = 'adc'

EXPLAIN yields this:

id        select_type    table        type    possible_keys    key      key_len    ref      rows     Extra
1         PRIMARY        <derived2>   ALL     (NULL)           (NULL)   (NULL)    (NULL)    17206    Using where
2         DERIVED        a            ALL     (NULL)           (NULL)   (NULL)    (NULL)    17743
3         UNION          b            ALL     (NULL)           (NULL)   (NULL)    (NULL)    5757
(NULL)    UNION RESULT   <union2,3>   ALL     (NULL)           (NULL)   (NULL)    (NULL)    (NULL)

In this case, part_manufacturer_clean and manulower are keys in both tables. When I don't use the subselects and union, and just use one table, everything works fine. I'm not sure if the issue is with the union or with the subselects. Is there any way to union two tables and still use keys/indexes for performance?


texts: Create MS SQL login for support engineer to access only vendor database

texts:
I have sql server 2005,
 I need to create separate database login to support engineer of my software vendor to access their own database. My only worry is deny him to take backup from our database while he connected remotely to resolve any incident in the application.

To sum up,

1- How to create login with access to specific database only
2- How to monitor his activity on database (what query he runs, is he try to took any backup and when)
3-Deny Any backup and restore task (I want to keep this only to sysadmin)


texts:
Has anybody seen evidence of the effect of -E when using SSDs?

The effect on 'spinning rust' drives is not disputed - but SSDs aren't really flustered by random I/O. I wonder if the -E option might even hurt performance.

On servers with a mix of drives (SSD SAN, PCI SSDs and traditional SAN) SQL Server has to decide on startup whether to use -E or not. I have some empirical evidence that the option might be detrimental to performance, but I'd like other people's feedback before I consider taking it off.

My setup uses standard 64K RAID stripes, and the NTFS cluster size is also 64K.


texts:
Our company has user data (name, email, phone number, systems of access, AD username) in a large Excel workbook. The Excel workbook is becoming very slow because of the 1000s of entries in it, and we're looking for an alternative solution.
I was think Microsoft Access because of the ease of use and ability to quickly make forms and reports, but my boss is worried about Access becoming end of lifed soon. Anyone have another solution for this? I know a little SQL, so that's an option, but not a preferred option.


texts: represent multiple entities to one entity relationship

texts:
If suppose, I have following tables:

Here, person can own either home or car. person can't own both at a time. If person owns home, then owns field will be equal to 1, if it owns car, owns field will be equal to 2.

Then, to retrieve what person owns, I will have to fire two queries in application like this:

select first_name, last_name, owns from person where id=1000;

if(owns == 1){
    select id, no_of_rooms, area, city from home where person_id=1000;
}else if(owns == 2){
    select id, make, model, color from car where person_id=1000;
}

Also, what I will do, after first query has executed, someone has deleted things for that person. Then I got in zombie state, I had known person information, but now have no idea about thing he owns.

Or may be I can do like this:

Then query will be:

select p.first_name, p.last_name, t.id, t.type_of_thing, t.no_of_rooms, t.area, t.city, t.make, t.model, t.color from person p, owned_thing t where p.id=1000 and p.id = t.person_id;

if(type_of_thing == 1){
    // process home in application
}else if(type_of_thing == 2){
    // process car in application
}

Which approach is better? Or is there any other better approach than this? I am going to use MySQL database.


texts: Deadlock: How to prevent Primary Key Lock in SELECT

texts:
In MySQL 5.7 we are running tracking queries every day at 0:00.

Lately, these are causing deadlocks with live calls that happen simultaneously which causes issues in our live service. (Or maybe I just started to notice this now..)

Tracking Call:

INSERT INTO track_daily (date, dau, mau, chips, subscriptions, trueskill,
                         results_canceled, origin)
SELECT DATE_SUB('17-05-31', INTERVAL 1 DAY), count(*), 14, 315, 1,
       if(count(*) = 0, 0, avg(mean - 3 * stddev)), 0.0299, 'facebook'
FROM   user
WHERE  lastLogin >= DATE_SUB('17-05-31', INTERVAL 1 DAY)
AND    user.origin = 'facebook'

Lock:

  RECORD LOCKS space id 1084 page no 6776 n bits 176 index PRIMARY of table data.user trx id 6727675275 lock mode S

Service Call:

UPDATE user
SET    chips = chips + -1, chipsWon = chipsWon + 0
WHERE  uid = '1448963' AND chips >= --1

Lock:

  RECORD LOCKS space id 1084 page no 6776 n bits 176 index PRIMARY of table data.user trx id 6727675426 lock_mode X locks rec but not gap waiting

I understand this is happening because both queries are locking the PRIMARY key of the user table, but I do not understand why the tracking call requires this lock. Can someone give me a tip how to optimize the tracking call so it does not lock the PRIMARY anymore?

Edit: The result from SHOW CREATE TABLE:

CREATE TABLE `user` (
    `uid` int(10) unsigned NOT NULL,
    `mean` double NOT NULL DEFAULT ''25'',
    `stddev` double NOT NULL DEFAULT ''8.33333333333333'',
    `trust` double NOT NULL DEFAULT ''1'',
    `roundsPlayed` int(10) unsigned NOT NULL DEFAULT ''0'',
    `roundsPlayedDaily` smallint(5) unsigned NOT NULL DEFAULT ''0'',
    `roundsPlayedLeague` int(10) unsigned NOT NULL DEFAULT ''0'',
    `roundsPlayedChips` int(10) unsigned NOT NULL DEFAULT ''0'',
    `dailyScore` int(11) NOT NULL DEFAULT ''0'',
    `leagueScore` int(11) NOT NULL DEFAULT ''0'',
    `alltimeScore` int(11) NOT NULL DEFAULT ''0'',
    `displayName` varchar(255) NOT NULL DEFAULT ''Neuling'',
    `imageUrl` varchar(255) NOT NULL,
    `chips` mediumint(8) unsigned NOT NULL DEFAULT ''17'',
    `chipsWon` mediumint(8) NOT NULL DEFAULT ''0'',
    `lastLogin` timestamp NULL DEFAULT NULL,
    `registered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `origin` varchar(20) NOT NULL,
    `banned` timestamp NOT NULL DEFAULT ''0000-00-00 00:00:00'',
    PRIMARY KEY (`uid`),
    KEY `leagueScore` (`leagueScore`),
    KEY `dailyScore` (`dailyScore`),
    KEY `chipsWon` (`chipsWon`),
    KEY `user_roundsPlayedLeague_idx` (`roundsPlayedLeague`),
    KEY `user_roundsPlayedChips_idx` (`roundsPlayedChips`),
    KEY `user_roundsPlayed_idx` (`roundsPlayed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


texts: I want to reset a column of the table after a month

texts:
I have a table e.g T1

I want to reset all rows where status = 0 and store these data (where status=0) in another table T2, but I want to do this after a month.
Kindly suggest what to do.

I am doing this in FEE Management system where a student will pay fee once in a month and after a month status should be "0" because I will use this table again for the next month. and i want to copy all data where status=1 in another table before reset the status column.


texts: Mysql update adding subquery field value to field

texts:
I want to update a field adding it the value of a subquery field.

I'm trying this:

UPDATE table a
SET a.field=(a.field+b.subfield)
INNER JOIN (SELECT subid, subfield FROM subtable WHERE ..... ) AS b
ON a.id = b.subid
WHERE ...

but I'm getting the syntax error.

How is the correct syntax for this operation?


texts: How to read and process xml data from stored procedure in sql server?

texts:
I am calling a stored procedure like this:

usp_ReportResults @query = '759,905,1048,170,725,80129',
                  @ReportName = 'GenRepot'

where usp_ReportResult is defined as

CREATE PROCEDURE [dbo].[usp_ReportResults]
     @query VARCHAR(MAX),
     @ReportName VARCHAR(100),
     @AutoSelectXML BIT = 1,
     @XMLResult XML = NULL OUTPUT

I am trying to get result in xml variable to make further process as:

DECLARE @XMLRESULT xml

SET @XMLRESULT = exec usp_ReportResults @query = '759,905,1048,170,725,80129', @ReportName = 'GenRepot'

But I'm not able to get result in @XMLRESULT and unable to read and store data result from xml to table.


texts: A little clarification on how 'LIMIT' works

texts:
Not being a full blooded member of the DBA clan, but more of a dev clan, I find myself trying to figure out some slow running queries.

The subject of my question however is not 'Why my queries are slow' , but one of wanting to know how the 'LIMIT' command in MySql works.

At the moment, I'm still trying to troubleshoot the server performance itself, so as a way of giving it a little SQL to chew on (Just to make it do some work) I'm using the following:

SELECT * FROM <table> LIMIT 10

The table I'm using is a live table with an excess of 50Mil rows in it, and as I'm only conducting tests at the moment my expectation is this will return the first 10 records off the top of the table, and would easily do so in under 10 minutes.

However...

I'm seeing this SQL still running after 30 mins or more, and appearing not to do anything.

What I want to clarify specifically is this:

Does MySql attempt to fetch the entire table from a DB before then just returning the first 10 rows

OR

Does MySql fetch rows as it receives them and stop after it counts that it's recieved 10 rows

Cheers
Shawty


texts: Identifying postgres db health tool similiar to mysqlcheck

texts: I'd like to run a check against databases in postgresql to verify that everything is ok after the server crash. On mysql it would be mysqlcheck. I would like to know the postgres equivalent.


texts: In Oracle AWR report how to use "Latch statistics" section in analysis

texts:
Latch statistics in AWR report contain following sections

1.Latch Activity
2.Latch Sleep Breakdown
3.Latch Miss Sources
4.Parent Latch Statistics
5.Child Latch Statistics

How can I use these sections of AWR report for useful insights to improve Oracle performance?


texts:
I have an order table, and in the order table I have a contact_id.

Now, each contact is linked to an account. For my order table, should I have both contact_id and account_id?

If I wanted to say, list all orders for account ABC, I could just look through all orders where account_id = "ABC's id"

If I had just the contact_id I would have to go through all the contacts and then pull orders based on each contact_id... this seems kind of like a pain in the neck.

I don't know if this is really a normalization question, but the easy way seems a little redundant, but I'm not 100% sure. What is the accepted best practices for doing something like this?

Thanks in advance.


texts:
As a general rule, should I be declaring all my stored procedures with the DETERMINISTIC keyword if they are indeed deterministic?

It seems to me that the majority of stored procedures will be deterministic. Am I right in thinking that the only non-deterministic procedures are ones that call non-deterministic functions like RAND() or CURDATE()?

Anyway, the reason I'm asking is because when I use the Data Restore feature within MySQL Workbench I get this error:

ERROR 1418 (HY000) at line 1209: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Is it better practice to simply set the 'log_bin_trust_function_creators=1' instead? Bear in mind that I have 50+ stored procedures.


texts: Why does "SELECT POWER(10.0, 38.0);" throw an arithmetic overflow error?

texts:
I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns.

As part of the check I compute the size of the data type's range for every IDENTITY column; I use that to calculate what percentage of that range has been exhausted.  For DECIMAL and NUMERIC the size of that range is 2 * 10^p - 2 where p is the precision.

I created a bunch of test tables with DECIMAL and NUMERIC IDENTITY columns and attempted to calculate their ranges as follows:

SELECT POWER(10.0, precision)
FROM sys.columns
WHERE
       is_identity = 1
   AND type_is_decimal_or_numeric
;

This threw the following error:

Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric.

I narrowed it down to the IDENTITY columns of type DECIMAL(38, 0) (i.e. with the maximum precision), so I then tried the POWER() calculation directly on that value.

All of the following queries

SELECT POWER(10.0, 38.0);
SELECT CONVERT(FLOAT, (POWER(10.0, 38.0)));
SELECT CAST(POWER(10.0, 38.0) AS FLOAT);

also resulted in the same error.

Why does SQL Server try to convert the output of POWER(), which is of type FLOAT, to NUMERIC (especially when FLOAT has a higher precedence)?
How can I dynamically calculate the range of a DECIMAL or NUMERIC column for all possible precisions (including p = 38, of course)?


texts: Failing MERGE Statement: How to monitor with XEvents and specifically get parameter values for a prepared statement?

texts:
My problem

I am running SQL Server 2017 and would like to investigate an error with a MERGE statement like the following

MERGE INTO table
USING
    (SELECT ? AS Search_Col) AS SRC
    ON table.blobTask_id = SRC.Search_Col
WHEN MATCHED THEN
    UPDATE SET
    x= ?,
    y= ?,
    z= ?
WHEN NOT MATCHED THEN
    INSERT (blobTask_id ,x, y, z, )
    VALUES (SRC.Search_Col, ?, ?, ?)

Error is

  Violation of UNIQUE KEY constraint 'IX_U_CCC_GDV_CONTENTCCC__blobTask_id'. Cannot insert duplicate key in object 'table'. The statement has been terminated.

I am aware that the MERGE statement comes with serveral gotchas. However I could outrule the typical issues ( no triggers, no concurrency on data modifications on the table).

Odd thing is that the Error just comes up just every once in a while and executing the statement again works without errors.

My diagnostic approach

I set up an Extended Events Session filtering for just the query_hash of the problematic statement. Jeremiah Peschka has a great blog post which I followed to do this.

Currently my XEvent Session looks like this:

CREATE EVENT SESSION [query hash] ON SERVER
ADD EVENT sqlserver.prepare_sql(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name)
    WHERE ([sqlserver].[query_hash]=(6577967268103212561.))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(1)
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text)
    WHERE ([sqlserver].[query_hash]=(6577967268103212561.))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name)
    WHERE ([sqlserver].[query_hash]=(6577967268103212561.)))
ADD TARGET package0.event_file(SET filename=N'X:\DB_Data\query_hash.xel',max_file_size=(5),max_rollover_files=(5),metadatafile=N'X:\DB_Data\query_hash.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

My question

Problem is that with this session I only get recordings like the following:

Somehow only sp_statement_completed Events show up.
In order to debug the problem it is vital to get to the parameters of the SQL.

Specific question: How do I modify the XEvent session to show me the param values?

I was hoping to get that information from the prepare_sql event but it doesn't show up in the session....perhaps due to my filter?

General request for help: If you know about other ways to effectively troubleshoot and alleviate the issue please let me know as well.

Your Help is much appreciated

Thank you in advance

Martin


texts: How to use CREATE TABLE ... AS with Primary Key in SQLite

texts:
From SQLite documentation for CREATE TABLE http://www.sqlite.org/lang_createtable.html:

  A table created using CREATE TABLE AS has no PRIMARY KEY and no
  constraints of any kind.

So is there any general way to create table with primary key & other index information ?


texts: Validating Excel Counts against Database Inserts

texts:
I have to design a SSIS package which uses Excel Source files and inserts the data into SQL Server Database.

The problem is that I've created the packages top transfer data, but now I need to validate whether the Number of Rows in Excel File and the number of Rows in Database are the same. usually a single package has to process 3-4 Excel Files and each file has 5-6 sheets in it.

I've searched over the internet and found various ways one of which was that I use 'Row Count' component in Data Flow Tasks but I think that its a useless step since all the rows which will be read from the excel source will surely be transferred to the Destination. I want to know all the Excel Row Counts BEFORE the Data Flow Tasks are executed.

Is there a way to do this?


texts: Index Strategy for MySQL Query that matches on one column and between two others

texts:
I have a table that contains a set of measurements for a continuous stream of processes. Although each process is individual, they are categorized into groups. The events have a start and end timestamp and a process group identifier.
The table structure is as follows (InnoDB, MariaDB 10):
Table Name: measurements
 CREATE TABLE `measurements` (
 `row_id` int(11) NOT NULL AUTO_INCREMENT,
 `process_name` varchar(100) COLLATE utf8_bin NOT NULL,
 `process_id` int(11) NOT NULL,
 `process_group_id` tinyint(4) NOT NULL,
 `measurement_1` float NOT NULL,
 `measurement_2` float NOT NULL,
 `measurement_3` float NOT NULL,
 `measurement_4` float NOT NULL,
 `start_timestamp` int(11) NOT NULL,
 `end_timestamp` int(11) NOT NULL,
 PRIMARY KEY (`row_id`),
 KEY `process_group_id` (`process_group_id`,
     `start_timestamp`,`end_timestamp`),
 KEY `process_id` (`process_id`)
) ENGINE=InnoDB
  AUTO_INCREMENT=7294932
  DEFAULT CHARSET=utf8 COLLATE=utf8_bin

I'm designing a query to obtain the sum of measurements 1,2,3 & 4 for all processes running within a group at a particular point in time so that the app can express each measurement for a specific process as a percentage of the total measurements in the group at that time. The start and end times of processes within a group are not synchronized and they are of variable length.
So for a process running in Group 5, at timestamp 1431388800
SELECT  SUM(measurement_1),
        SUM(measurement_2),
        SUM(measurement_3),
        SUM(measurement_4)
    FROM  measurements
    WHERE  process_group_id = 5
      AND  1431388800
           BETWEEN start_timestamp
               AND end_timestamp

This query runs, but takes around 0.5s. The table has 8m records and grows by about 30,000 a day.
I have an index on process_group_id, start_timestamp, end_timestamp. However, the query does not appear to use anything but the process_group_id part of the index. I created an additional index on process_group_id alone to check this, and once created EXPLAIN showed it using this index.
After some searching, I saw a suggestion to modify the query and add an ORDER BY clause. Having done this the query is accelerated to around 0.06s and it seems to use the full index. However, I'm unsure as to why:
SELECT  process_group_id,
        SUM(measurement_1),
        SUM(measurement_2),
        SUM(measurement_3),
        SUM(measurement_4)
    FROM  measurements
    WHERE  process_group_id = 5
      AND  1431388800
  BETWEEN start_timestamp
      AND end_timestamp
    ORDER BY  process_group_id ASC

With 30,000 new records a day that requires their shares to be calculated, 0.06s is still not particularly fast. Is there a better way of structuring either the table or designing the query to get a few orders of magnitude quicker, or is a query which matches on one column and then a range query on two others always going to be fairly slow to run?


texts: Easiest Way to Migrate data from MySQL to MongoDB

texts:
My latest IT project has me working with MongoDB for the first time. While I have a bit (very little) experience with NoSQL, I have always mostly worked with MySQL using Workbench.

I need to migrate the data from a MySQL database into MongoDB as my employer is switching over from the former to the latter.

Can someone suggest what might be the easiest way to do that?

Thanks a bundle!


texts: Immigrate to file-per-table mode in MySQL

texts:
I have 20 MySQL databases and in each there are about 20 tables. All the data is saved in a file called ibData1. Now I discovered about the file per table mode but if I change the value in the my.ini file what would happen?

How can I immigrate to file-per-table mode and change all the data structure?

I prefer to have a directory for each Database where each table is saved in a different file.


texts: Compare two similar Postgres databases for differences

texts:
I occasionally download publicly available data sets in the form of Postgres dBs.   These datasets are updated/modified/expanded over time by the repository host.

Is there a Postgres command or tool (ideally FOSS) that can show the differences between an OLD and NEW Postgres database ?  (A working assumption is that 95% of the entries are unchanged and that the tables and relationships also do not change).


texts: Is it normal for disk activity to disturb MySQL performance?

texts:
I'm running MySQL 5.5 on a server with two SSDs in software RAID-1 configuration. The filesystem is ext4.

When any process (external to MySQL) produces a large amount of disk IO on the server, I find that MySQL becomes virtually paralyzed, with queries that normally take 30ms taking 10 seconds or more, depending on how long the IO lasts.

I can easily reproduce the problem by running a dd write test as follows:

dd if=/dev/zero of=tempfile bs=1M count=3024 conv=fdatasync,notrunc

This writes a 3GB test file to the same disk and partition where MySQL's data and log files are stored. Here is the output from the command:

3024+0 records in
3024+0 records out
3170893824 bytes (3.2 GB) copied, 16.0938 s, 197 MB/s

When I execute the command, I can immediately see queries showing up in the MySQL slow query log. Not surprisingly, they are all insert/update queries, which I assume are waiting to be committed. However, to my huge surprise, the query times are enormous: some are nearly as long as the execution time of the dd command itself!

It seems as if MySQL is paused or locked during any intense IO happening on the server. I thought that running MySQL on SSDs in RAID-1 would be exceptionally fast, with other IO operations unlikely to have any large effect on MySQL.

Is it normal for a single write process like this to disturb MySQL so profoundly?
Should I be looking at problems with the software RAID-1 or the ext4
filesystem?
Can MySQL be configured differently? I've read about setting
innodb_read_io_threads and innodb_write_io_threads to take
advantage of the higher IOPS available with SSDs, but I'm not sure
that will solve this problem.
Is it necessary to store all MySQL-related files on a dedicated disk to avoid this problem?

Thanks very much for any help.


texts: Unable to add db_datawriter to user defined role

texts:
I want to grant a user defined role, read and write permissions to the entire database and add users to it. I was unable to do this using GUI. So I tried the solution given at the link - https://www.sqlservercentral.com/Forums/Topic1141208-1526-1.aspx but it gives me the error mentioned below when I try to add db_datawriter to the role I created.

Cannot use the special principal 'db_datawriter'

The command I executed - EXEC sp_addrolemember 'DR_User', 'db_datawriter'

Please help me figure out the right way to achieve this.


texts: mongodb conf node won't start - compatibility error

texts:
I recently upgraded a dev sharded-cluster from 3.6 to 4.0 - one of the conf servers is now failing to start:

  2020-01-30T13:19:02.972-0600 F  CONTROL  [initandlisten] ** IMPORTANT:
  UPGRADE PROBLEM: Found an invalid featureCompatibilityVersion document
  (ERROR: BadValue: Invalid value for version, found 3.6, expected '4.2'
  or '4.0'. Contents of featureCompatibilityVersion document in
  admin.system.version: { _id: "featureCompatibilityVersion", version:
  "3.6" }. See
  http://dochub.mongodb.org/core/4.0-feature-compatibility.). If the
  current featureCompatibilityVersion is below 4.0, see the
  documentation on upgrading at
  http://dochub.mongodb.org/core/4.0-upgrade-fcv.

The upgrade from 3.6 -> 4.0 reported as successful - I checked the data shards but didn't think to check each conf server... Today, I just upgraded the mongo binaries and on reboot, encountered the problem with one of the conf servers failing to start with the above mentioned error.  The cluster had been running at 4.0 for weeks before I noticed this problem following the update reboot.

The other two conf servers (running) are reporting that they're at 4.0 so life is good there, as are all of the data shards.

I cannot start the conf server without encountering this error which shuts-down the server making it impossible to issue the set-feature-compatibility-version directive from mongos.

Since the other two conf nodes are running and reporting the correct release version, would it be best to just nuke the down-server's data, attempt to restart the node, and then issue the command to ensure that the restarted node's data version is correct?  Or, is there some sort of force command that would bypass the version check?

TIA!


texts: In MySQL, when using InnoDB, what happens to the way data is laid out on disk when the primary key is dropped? Also, what happens when a new primary key is added?


texts:
Recently devops guy from our company told me that our local mysql stage environment are much faster than aws, because local disk has 10k iops, but our aws rds has only 500 iops.

As far as I understand aws iops states for throughout capacity, not the disk speed. And still ssd disk io same 10k iops or more? And considering that the load is less than 500 iops, rds might be much faster?


texts: What intersection do multiple inner joins return?

texts:
If I have a query:

SELECT * FROM A
INNER JOIN B ON B.ID = A.ID
INNER JOIN C ON C.ID = B.ID

Would that return a different result to this query?

SELECT * FROM A
INNER JOIN B ON B.ID = A.ID
INNER JOIN C ON C.ID = A.ID

It seems to me that in the first query I would get the intersection of A,B,C. And in the second query I would get the intersection of A,B, and the intersection of A,C.

If that were the case, would I find NULL values in the columns for B/C where there is no intersection between B,C?


texts: How to definitively clear away database before restoring from new version of backup file

texts:
We are a team of mathematicians (i.e., no DBA experience).

We are migrating from an old server box (SQL Server 2012) to a new server box (SQL Server 2017).  For each database on the old server, we made a backup copy, ported it over to the new server, and restored it to its full database glory from the backup file.

So far, so good.

However, there was a delay in our move-in date and we are still using the old server.  As we continue to work on the old server, changes continue to accrue in those databases.  So it means that the copied databases sitting on the new server are now obsolete and out-of-date.  (Ironic, isn't it?)

For those databases that have changed, we know we will eventually have to make new backup copies and port those over to the new server.

When it comes time, what is the smartest way to get rid of those now-obsolete databases on the new server before restoring the latest backup copies?

We worry that with the same names, some occluding or eclipsing might occur if we are not careful.  We want to make sure that the out-of-date copies on the new server are well and truly gone before restoring the latest versions.


texts: How do you perform a MYSQL After Update Trigger Insert Loop?

texts:
I have 3 mysql tables, one called batches, one called base_formula and one called raw_materials_stock.

I want to perform an after update trigger on the batches table to insert information into the raw_materials_stock table based on information from the base_formula table.

Batches Table

batch_id, complete_dt, base_id

Base Formula Table

base_id, raw_material_id, percentage

Raw Material Stock Table

batch_id,raw_material_id,use_date,quantity

Batches Trigger

IF OLD.complete_dt is NULL and NEW.complete_dt is not null and (select b.raw_material_id from base_formula b where b.base_id=new.base_id) is not null  THEN

INSERT INTO raw_materials_stock (batch_id,raw_material_id,use_date,quantity)
VALUES(
new.batch_id,
(select b.raw_material_id from base_formula b where b.base_id=new.base_id),
now(),
(select ((new.volume/100)*b.percent)*-1 from base_formula b where b.base_id=new.base_id));

END IF

Issue

This issue i have is the base_formula table has more than 1 row with the same base_id. How can i use a loop to insert all of the rows with the same base_id into the raw_material_stock table?


texts: install postgresql without creating instance (for use with repmgr)

texts:
I'm trying to get REPMGR setup, and I'm following the steps at https://repmgr.org/docs/current/quickstart-standby-preparation.html to get the standby setup.

I noticed it warns On the standby, do not create a PostgreSQL instance.  However I believe this happens automatically with how I installed Postgres

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt -y install postgresql-12 postgresql-client-12

because when I try to clone the primary onto the standby as mentioned in https://repmgr.org/docs/current/quickstart-standby-clone.html
$ repmgr -h node1 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone --dry-run

I get

postgres@empty2:~$ repmgr -h 192.168.1.102 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone --dry-run
NOTICE: destination directory "/var/lib/postgresql/12/main" provided
ERROR: specified data directory "/var/lib/postgresql/12/main" appears to contain a running PostgreSQL instance
HINT: ensure the target data directory does not contain a running PostgreSQL instance

Now I'm just ASSUMING that this is because the database instance was created when I installed postgres on the standby. and I'm also ASSUMING that I can just delete everything in the data directory on the standby, and everything will work ok...

But (assuming my assumptions are correct....) what is the correct way to install postgres12 without creating an instance and the corresponding data files?


texts: Database mail in Azure VM with SQL Server installed on it

texts:
We migrated 4 servers with SQL Server instances to Azure VM around 2-3 months ago. We configured "database mail" the same way we did for the on-prem servers. Mail worked fine immediately. All of a sudden, yesterday around 4 pm, mails were not able to be sent.

Error is: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2020-02-06T08:20:42). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond :25).)

We have researched and all we find is that we should not use port 25, but when we change it to port 587 it failed as well.

The settings we use to configure database mail account are:

Server name: .mail.protection.outlook.com
Port: 25
SMTP Authentication: Anonymous.

By the way, the servers we still have on-premises are working fine using the exact same settings.

As a workaround, we have configured sendgrid to send mail for the azure servers, but my question is if someone can help us to configure the SQL Server database mail correctly in the azure vm using the office 365 exchange.

Thanks!


texts: Tracking Down Application Timeout Errors in SQL Server

texts:
SQL Server 2008 SP3

How do I track down these timeout errors ?

The errors are displayed on an intranet dashboard used specifically for error reporting in IIS.  My suspicion is that there is a default timeout of 30 seconds in the web application and if a query takes more than thirty seconds, an exception is thrown. As there are many queries that take longer than 30 seconds on these SQL servers, I can't just filter in profiler based on duration.

Serving up the website being monitored by this dashboard are two IIS servers retrieving data from seven SQL Server instances.

Could I use the "User Error Message Event" and the "OLEDB Errors Event" to track these errors in SQL Server Profiler?


texts: in what all scenarios SQL Server update statistics

texts:
I know SQL Server update stats when 20% of data is changed in a table. It keeps tracks of it by using modification counter which we can see in [modification_counter] column of sys.stats table.

But I can see for one of my key table in the database that stats are getting updated much before they reach 20% data modification threshold. Just wanted to know is there any thing else which will force SQL Server to update stats automatically.

Why I am worried is that when SQL Server update stats automatically, it is using 5% sampling rate which might cause creation of bad / inaccurate execution plan. (need confirmation on that is totally different issue).

So I want to know why SQL Server is so often updating stats?


texts: SQL Job Agent Service Account - Access is denied to run Powershell

texts:
I am trying to run a PowerShell script via the command line via SQL Job.

"C:\Windows\System32\WindowsPowerShell\v1.0" "C:\Test\TestScript.ps1"

I am running the SQL Job from a SQL Server 2008 R2 server. Which permissions does the SQL Job Agent Service Account need to run a PowerShell script? Or is this a windows permission level, not a db permission?

EDIT:
This is the error I am getting
Executed as user: ****.

  The process could not be created for step 3 of job ## (reason: Access
  is denied). The step failed.


texts:
I am running into a problem now, i have an oracle database that is stored inside a server and now because of the sudden blackout the c drive is messed up. The drive is partitioned, windows on c and oracle on drive e.

The oracle installation folder is fine on E:\app. Now i have to restore that database on the another server to get it running. The problem is i cant boot windows on that drive.

Anybody can help me on restoring the database from oracle installation folder ? I know i cant run rman because rman can only be run from command line only which i cant run it now.


texts: Service privileges for Patch Download? (Oracle 11gR2)

texts:
I first asked this question on stackoverflow, but someone pointed out that this might be the appropriate site. Hope I am doing this right.

To perform tests in advance of an upgrade from Oracle 11gR2 to 12c on our production systems, I tried to update the database of a developer virtual machine (Windows 7). The current Oracle Database version of this old developer machine is 11.2.0.1.0.
The setup file of Oracle 12c then told me to patch to 11.2.0.2, 11.2.0.3 or 11.2.0.4 first. So I tried to find and download the Patch to 11.2.0.4 for Windows x64. The official Oracle page only lists 11.2.0.1, so I did a quick search that lead me to this page.

I am used to be forced to login to download stuff from Oracle, but the second page tells me I need special support privileges. Seriously? Oracle allows me to setup a developing machine with their enterprise editions but I need support privileges to download a patch to the most recent version of Oracle 11?

Did I miss something? How am I supposed to test the upgrade process on my developing machine(s)?

Thanks for any help!


texts:
I am trying to get all accounts with duplicate end dates. I need info from four tables that only have the FK LDC_ACCT_ID in common. When i join the tables i get tons of dupes. I understand why but I cant get around it. Help!! Here is the query:

In SQL Server 2014

SELECT l.ALTERNATE_ID
    ,l.LDC_ACCT_NO
    ,ia.LDC_ACCT_ID
    ,count(ia.END_DT) AS SRVC_END
    ,mu.QTY_QUAL_CD
FROM LDC_ACCOUNT l
JOIN INVOICE_ADVISE ia ON l.LDC_ACCT_ID = ia.LDC_ACCT_ID
JOIN MONTHLY_USAGE mu ON l.LDC_ACCT_ID = mu.LDC_ACCT_ID
JOIN LDC_STATUS_HISTORY sh ON l.LDC_ACCT_ID = sh.LDC_ACCT_ID
WHERE sh.STATUS_CD NOT IN (
        'FIN'
        ,'TERM'
        ,'PEFI'
        )
    AND mu.QTY_QUAL_CD = 'QD'
GROUP BY ia.LDC_ACCT_ID
    ,l.LDC_ACCT_NO
    ,l.ALTERNATE_ID
    ,ia.END_DT
HAVING (count(ia.end_dt) > 1)
ORDER BY ia.LDC_ACCT_ID DESC


texts: Faster way to do an Update which includes a window function

texts:
I am looking for a faster way to do a update query which includes a window function, and don't have any solid idea what might help.

I am "denormalizing" a bit of data, which means moving the grandparent id into the child, and adding a sequence number.  Prior to this we had no "grandparent" (crate) level, only "parent" (box).

The schema is quite simple, in this case:

crate[id] =>
    box[id] =>
        unit[id, sequence_in_box]
           adding [crate_id, sequence] to `unit`

The query I have runs fairly slow (to me), only updating around 10k rows a second (200k rows, ~22 seconds).  Since some instances of the database have 2M rows, I'm hoping to make this update go a bit swifter.

UPDATE unit dest SET
    crate_id = src.crate_id,
    sequence = src.sequence
FROM (
         SELECT u.id, b.crate_id, row_number() OVER w AS sequence
         FROM   unit u INNER JOIN box b ON u.box_id = b.id
         WINDOW w AS (
             PARTITION BY b.crate_id ORDER BY b.id, u.sequence_in_box
         )
     ) as src
WHERE
    dest.id = src.id

Sample Existing data (with desired update) looks something like this, with some field names abbreviated.

crate    box            unit
id       id, crate      id   box  seq   crate_id, sequence
-----    ---------      --------------  -------------------
1        1   1          1    1    1     1         1
2        2   1          2    1    2     1         2
         3   2          3    2    1     1         3
                        4    2    2     1         4
                        5    3    1     2         1
                        6    3    2     2         2

I can't use UPDATE x FROM because window functions aren't allowed in an update statement (and I'm not sure that would be faster anyway.)  I am prepared for a "nope, no better way", just hoping its not true :D


texts: How to search entire database to find tables that contain 2 columns?

texts:
This database query doesn't work:
select table_schema,
table_name,
column_name,
column_name2
from information_schema.columns
where column_name = 'user_id' and column_name2 = 'organisation_id'
and table_schema = 'brand_production';

I would like to search for each table in the database to see if it contains "user_id" and "organisation_id"?


texts: Is MySql locking table during backup and how to avoid?

texts:
I have a scheduled backup that runs daily in MySQL and I have noticed that during the backup a lot of queries that query a frequently used table seem to take forever to complete. This causes the website to lag like crazy during the backup and once the backup completes, specifically for the frequently used table, the site runs fast again.

Does MySQL lock table(s) during the backup and if so is there anyway to avoid this?

How do other big systems handle backups without disrupting / slowing their applications?

Thanks


texts: Reconcile users with logins using 'sp_change_users_login'

texts:
Sql Server 2008 (and 2005):

I have a Sql Server instance, let's say, InstA, in a Domain (called AD). In that I have a domain Windows Authentication Login, called AD\Log1 that has an associated user, User1 in a database called DB1.

So, now I backup this database and restore on a new instance (Inst2) that is not part of any domain. It has an existing login (LocalMachine\Log2) that I need to have mapped to the existing User1 in the newly restored database DB1.

With sp_change_users_login, I get the following error:

EXEC sp_change_users_login 'Update_One', 'User1', 'LocalMachine\Log2'

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'User1' is absent or invalid.

If I use ALTER USER User1 WITH LOGIN = 'LocalMachine\Log2', it renames the user User1 to LocalMachine\Log2 and maps it to LocalMachine\Log2 login. It works, but I do not want it to rename.

I would prefer to map this existing database user User1 in the database DB1 to an existing login in the new instance inst2 without changing the user name or creating a new login (AutoFix). Just update the SID and map it (without other changes).

Is it possible?

What I gathered so far is, for my situation, sp_change_users_login does not work because LocalMachine\Log1 is a Windows Account (not a Sql Login). but if both were Sql Logins, sp_change_users_login with work perfectly.

Thanks,


texts: We are using mysql 5.7.22 and I have created a user with drop privilege because this user using temp tables and temp tables needs drop privilege if table already exist. Due to this privilege, user can drop table or any other object. I want user can't able to execute drop table command from client side. Is there any work-around ?


texts: Not getting satisfactory performance improvement with NDB Cluster

texts:
Since last few days, I am trying to improve performance of MySQL NDB Cluster but can't figure out what I am missing/being mistaken in defining/tuning parameters.

I have these configurations running right now:

2 data nodes, 2 management nodes, 2 API nodes, 6 API slots(used by ndb_connection_pool).

Config.ini file for management node is as given below:

#
# Configuration file for MyCluster
#

[NDB_MGMD DEFAULT]
Portnumber=1186

[NDB_MGMD]
NodeId=49
HostName=hazelcasta
DataDir=/home/mysql/mysqlcluster_data/49/
Portnumber=1186

[NDB_MGMD]
NodeId=52
HostName=hazelcastb
DataDir=/home/mysql/mysqlcluster_data/52/
Portnumber=1186

[TCP DEFAULT]
SendBufferMemory=4M
ReceiveBufferMemory=4M

[NDBD DEFAULT]
BackupMaxWriteSize=1M
BackupDataBufferSize=16M
BackupLogBufferSize=4M
BackupMemory=20M
BackupReportFrequency=10
MemReportFrequency=30
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15
#DataMemory=224M
#IndexMemory=40M
DataMemory=512M
IndexMemory=128M
MaxNoOfTables=4096
MaxNoOfTriggers=3500
NoOfReplicas=2
StringMemory=25
#DiskPageBufferMemory=64M
DiskPageBufferMemory=512M
#SharedGlobalMemory=20M
SharedGlobalMemory=384M
LongMessageBuffer=32M
MaxNoOfConcurrentTransactions=100000
BatchSizePerLocalScan=512
FragmentLogFileSize=256M
NoOfFragmentLogFiles=3
#RedoBuffer=32M
RedoBuffer=64M
# added new
DiskIOThreadPool=16
# commented and added for only data node 2
#MaxNoOfExecutionThreads=2
StopOnError=false
#LockPagesInMainMemory=1
TimeBetweenEpochsTimeout=32000
#TimeBetweenWatchdogCheckInitial=60000
TransactionInactiveTimeout=60000
HeartbeatIntervalDbDb=1500
HeartbeatIntervalDbApi=1500
MaxNoOfOrderedIndexes=3000
MaxNoOfAttributes=5000
TransactionDeadlockDetectionTimeout=5000
LcpScanProgressTimeout=300
TimeBetweenWatchdogCheck=300000
TimeBetweenWatchdogCheckInitial=300000
LockPagesInMainMemory=2
MaxNoOfLocalOperations=200000
MaxNoOfConcurrentOperations=150000
ThreadConfig=ldm={cpubind=2},rep={cpubind=3},tc={cpubind=4},recv={cpubind=5},send={cpubind=6},main={cpubind=7},io={cpubind=7}

[NDBD]
NodeId=1
HostName=dba
DataDir=/home/mysql/mysqlcluster_data/1/

[NDBD]
NodeId=2
HostName=dbb
DataDir=/home/mysql/mysqlcluster_data/2/

[MYSQLD DEFAULT]

[MYSQLD]
NodeId=55
HostName=10.10.16.11

[MYSQLD]
NodeId=57
HostName=10.10.16.12

[MYSQLD]
NodeId=58

[MYSQLD]
NodeId=59

[MYSQLD]
NodeId=60

[MYSQLD]
NodeId=61

[MYSQLD]
NodeId=62

[MYSQLD]
NodeId=63

And my.cnf file for API nodes is as given below:

#
# Configuration file for MyCluster
# Generated by mcc
#
[mysqld]
log-error=mysqld.55.err
datadir="/home/mysql/mysqlcluster_data/55/"
tmpdir="/home/mysql/mysqlcluster_data/55/tmp"
basedir="/usr/local/mysql/"
port=3306
ndbcluster=on
ndb-nodeid=55
ndb-connectstring=hazelcasta:1186,hazelcastb:1186,
socket="/home/mysql/mysqlcluster_data/55/mysql.socket"
user=root
explicit_defaults_for_timestamp=TRUE
lower_case_table_names=1
max_connections=300

# pooling
ndb_cluster_connection_pool=4

#Added following paramters for InnoDB Performance Improvements
innodb_buffer_pool_size=2g
innodb_log_file_size=256M
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_file_per_table
#Finished adding InnoDB Performance Improvement parameters

# Added following parameters by Parimal for Join Performance improvements
optimizer_switch='index_condition_pushdown=off'
optimizer_switch='mrr=on'
optimizer_switch='mrr_cost_based=off'
optimizer_switch='batched_key_access=on'
join_buffer_size=6M
read_rnd_buffer_size=6M
thread_cache_size=1024
# Finished adding by Parimal Join Performance improvements

Can you please take a look and suggest any most important changes that I have missed ?


texts: LDF and MDF file sizes for a dynamically created Database

texts:
I am creating a dynamic database from C# code, and would like the database to have a fixed size (for example 5 GB). While creating the DB through code, I need to specify MDF and LDF max file sizes. What should be the ideal values? 2.5GB each or is their some other ratio?

I have to make sure that the total DB size does not go more than 5GB, so should I only worry about the MDF size (keeping it fixed at 5GB) and leave the LDF size to grow unlimited (I cant do that but if it hurts performance then I have to think about it)?

Thanks,
Vivek


texts:
I loaded the stored procedure into master and ran:

dbo.sp_BlitzFirst @ExpertMode = 1

I got:

Setting up configuration variables
Msg 207, Level 16, State 1, Line 5
Invalid column name 'database_id'.
Now starting diagnostic analysis
Capturing first pass of wait stats, perfmon counters, file stats
Beginning investigatory queries
Finished running investigatory queries
Waiting to match @Seconds parameter
Capturing second pass of wait stats, perfmon counters, file stats
Analyzing changes between first and second passes of DMVs
Analysis finished, outputting results
Msg 207, Level 16, State 1, Line 5
Invalid column name 'database_id'.

kit version: SQL-Server-First-Responder-Kit-20161210

Microsoft SQL Server Enterprise Edition (64-bit) version 10.50.6000.34


texts:
I have a table that contains User information from different countries. Information like - Name, Country, LastLoginDate, IsUpdated, Certification.

This table has 1.5 million rows. Now to filter this, I have a stored procedure that takes input as -
UserNames = 'John, Matt, Robin'

And the query looks like -

SELECT Name, Country, LastLoginDate FROM Users
WHERE find_in_set(Users.Name, UserNames) <> 0
;

The EXPLAIN statement says that this has type - ALL and doesn't use any index. Is there any way I can take the text and create a table with input values and join to my core table which will help using index and faster ouput?