texts: Add replica from node 1 can't find node 2 while installing an Always ON AG lab on virtual machines

texts:
I am trying to set up an Always ON availability group lab on virtual machines, with Windows Server 2016 and MS SQL Server 2017.

While creating a new Availability Group on node 1 by the wizard, I meet difficult that the system can not find the node 2 in the step of validation.

The errors show as:

  'The following required directories do not exist on replica WINN02\SQL_AG_02 : C:\Program Files\Microsoft SQL
  Server\MSSQL14.SQL_AG_01\MSSQL\DATA.
  (Microsoft.SqlServer.Management.HadrModel)'

and

  'The primary server 'WINN01\SQL_AG_01' cannot write to '\Winn01\nzfagsharefolder\BackupLocDb_cbbf8fb2-f6e8-4aa9-8995-675364932027.bak'.
  (Microsoft.SqlServer.Management.HadrModel)

Additional information

  Backup failed for Server 'WINN01\SQL_AG_01'.
  (Microsoft.SqlServer.SmoExtended)

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  Cannot open backup device
  '\Winn01\nzfagsharefolder\BackupLocDb_cbbf8fb2-f6e8-4aa9-8995-675364932027.bak'.
  Operating system error 5(Access is denied.). BACKUP DATABASE is
  terminating abnormally. (Microsoft SQL Server, Error: 3201) For help,
  click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3076&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476'.

Attached the error details:

The problem seems about connection, so I made some connection test.

When I try to connect instance node 2 via computer name, in node1, it shows connection error. Attached the screenprint:

.

If I change it the computer name of node 2 into IP address, it can be connected easily.
If I ping the address and computer name in cmd of node 1, both of them are successful. Which means the connecting condition is good. Attached the screenprint:

I am stuck. Please help me, thank you!


texts: Oracle installation fails due to ORA-00443

texts:
I hope that you help me to resolve my problem.
I'm trying to install Oracle 11g on RHEL 6.
All prerequisites are ok, but I'm getting such error during step "Creating and starting Oracle instance". The error text is ORA-00443: background process "DIAG" did not start

Sometimes installation fails on 8%, sometimes on 44%. It's very strange. I thought it's related to RAM but I have enough memory (10Gb).

I tried to retry installation several times and sometimes it shows me that another background process (not DIAG, but RDBM for example) did not start. It seems that second part of error is random. But the error code ORA-00443 remains the same.

Here are some information from logs which says nothing to me where is the root cause of a problem

[Thread-10] [ 2013-12-17 23:04:34.027 EET ] [CloneDBCreationStep.executeImpl:460]  calling zerodbid
[Thread-10] [ 2013-12-17 23:04:41.620 EET ] [CloneDBCreationStep.executeImpl:470]  Shutdown database
[Thread-10] [ 2013-12-17 23:04:41.622 EET ] [CloneDBCreationStep.executeImpl:492]  Startup ......nomount......
[Thread-14] [ 2013-12-17 23:07:03.433 EET ] [BasicStep.handleNonIgnorableError:430]  oracle.sysman.assistants.util.UIMessageHandler@1719f30:messageHandler
[Thread-14] [ 2013-12-17 23:07:03.433 EET ] [BasicStep.handleNonIgnorableError:431]  ORA-00443: background process "DIAG" did not start

I tried to find solution within all possible resources, but no luck. Please help to resolve issue. Thanks in advance.


texts: Calculating percentile using counts of occurrences

texts:
Example table:

Speed       Count
102         3
201         2
205         9
208         4
301         1
303         2
307         6

Count is the number a specific speed is measured.

I need to calculate the percentile for the column Speed but not on the Speed values in the table but for the values times the count of those values.

Written out the data looks like this:

102,102,102,201,201,205,205,205,205,205,205,205,205,205,208,208,208,208,301,303,303,307,307,307,307,307,307

I know how to calculate the percentile on this set of data. But is there a way to use the table data without first transforming it in one huge set of individual numbers? The sum of Count in the real data runs in the billions so transforming the data in individual values would result in a (temporary) dataset of billions of rows.

If transformation is nevertheless necessary, what is the easiest way to do this?

I'm using SQL Server 2016


texts: Do my metrics justify changing the MAXDOP settings?

texts:
I recently took over a dual instance virtualized SQL Server with 8 x vCPUs and 8 x physical cores.  This is the only VM on the host.

MAXDOP is at the default setting.

The server has 100 high transaction vendor specific databases with some very nasty reporting queries.

CXPacket waits are at the top of the waits list with 63%.

Signal Waits are averaging 15.50%.

In PerfMon the Processor Queue Length rarely breaches 1.

Does this justify setting MAXDOP to 4 or 6?


texts:
Let's consider a table like that:

id |            name
 1 | This is a test sentence
 2 | is a sentence TeST
 3 | This a IS test sentence
 4 | iS a tEst sentence tHis
 5 | This a test sentence is

How could I achieve to get all the rows, searching for "is a sentence test"?

I am using:

SELECT *
FROM table
WHERE name ILIKE '%is%' AND name ILIKE '%a%' AND name ILIKE '%sentence%' AND name ILIKE '%test%'

But that doesn't seem to be the best approach.


texts: ORA-12516 but max_utilization not reaching limit

texts:
Our app began to raise ORA-12516, TNS:listener could not find available handler with matching protocol stack errors, and I'm trying to diagnose why.

The workload has not changed, so I think we might have introduced a bug in the application code, but I can't see why. The most obvious would be a connection leak, or unclosed idle transactions, but:

we are using a jdbc pool (tomcat-jdbc-pool), and the max active value is 73, below the 150 processes Oracle is configured to use. So I would have thought the pool would throw an error before Oracle hits its own limits.
do select * from gv$resource_limit; here is what I get:

NST_ID RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
---------- ------------------------------ ------------------- --------------- ------------------ -----------
         1 processes                                       32              40        150                150
         1 sessions                                        32              45        248                248
...

So it seems max utilization never hits the limit.

Does anyone have a tip on how to track this down?

EDIT: I made some progress on this. It turns out that tomcat-jdbc-pool has a query to check connections health. This query was configured as select 1 from dual;. Notice the semi-column, as this is the culprit: removing it fixes the app. I still have to find out what really happens with the ; at the end of the check query. Any advice is welcome.


texts: What function to use with this timestamp?

texts:
I have timestamp (stored as BIGINT) format I cannot turn into a human-readable format:

1462975819250

It points to a date in 2016-may-11th. However function to_timestamp() turns this into:

48329-11-10 13:00:49.999872+01

I'd like to create a VIEW where I could display these values, don't know what function to use.

The field is written by a Java program (EclipseLink I presume). I don't have the source-code of the program.


texts: I have a database schema from MySQL which I want to import to SQL SERVER, I have tried dumping from MySQL with compatibility for SQL Server, but with no success. Tried dumping as Excel still no dice. Does anyone know another way to import this database with no records from MySQL to SQL SERVER 2012? Thanks for your time.


texts: Numeric data type with variable significant digits

texts:
I need to implement a data type which should preserve the last zeros in the decimal points in SQL Server 2008 R2. Example of the data are

1.002
1.210
0.00100
0.1401

My clients get lab measurements like these and the number of decimal points tell what typee of instrumentation, methods etc. the lab used. They require to store the data as it is (not 1.21 for 1.210). For now I have a custom data type which implements varchar type as a base type, but the developers do not like it as the conversion to use mathematical operators involve some overhead. Is there any numeric type solution to handle this type of issue?


texts: How to display True & False for 1 & NULL

texts:
In my query I am able to get column values as 1 and NULL. I would like to display True & False for 1 & NULL?

/*Declare Variable*/
DECLARE @Pivot_Column [nvarchar](max);
DECLARE @Query [nvarchar](max);

/*Select Pivot Column*/
SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ QUOTENAME(PracticeLocationServiceCode) FROM
(SELECT DISTINCT [PracticeLocationServiceCode] FROM PracticeLocationService)Tab

/*Create Dynamic Query*/
SELECT @Query='
;WITH ServiceOffered
AS
(
SELECT PracticeLocationID, '+@Pivot_Column+'FROM
(SELECT PracticeLocationID, PracticeLocationServiceCode,CAST(ServiceFlag AS INT) ServiceFlag FROM PracticeLocationService )Tab1
PIVOT
(
MAX(ServiceFlag) FOR [PracticeLocationServiceCode] IN ('+@Pivot_Column+')) AS Tab2
)
SELECT REGION,LocationShortName,GLCODE,C.* FROM ServiceOffered C
INNER JOIN VWLOCATIONS V ON V.PracticeLocationID = C.PracticeLocationID
ORDER BY REGION,LocationShortName
'
/*Execute Query*/
EXEC  sp_executesql  @Query


texts:
In order to quickly calculate some features for later calculations I have attempted to store a table that allows looking up distances between locations that have been pre-calculated. I am using Python and SQLalchemy. I have 76,807,696 rows in my table. However a single query is taking upwards of 300 seconds.

My Database is defined as:

class LocationsLookupTable(db.Model):
    """Use this class to create the database Model for the locations lookup
table.
This class is used for creating the database table Model for the locations
lookup table. It will store the ID pair and their distance value. There
will also be a unique ID for each value in the database table.
"""

id = db.Column(db.Integer, primary_key=True, unique=True)
firstId = db.Column(db.Integer, nullable=False)
secondId = db.Column(db.Integer, nullable=False)
firstLocationGeoPointX = db.Column(db.Float)
firstLocationGeoPointY = db.Column(db.Float)
secondLocationGeoPointX = db.Column(db.Float)
secondLocationGeoPointY = db.Column(db.Float)
distanceValue = db.Column(db.Integer)

And I am querying it using the following query:

result = models.LocationsLookupTable.query.filter_by(
            firstId=firstId, secondId=secondId).first()

I have some theories as to why the performance is so bad.
The first is that I simply have too many rows.
The second is that a database is not suited for this task.
The third is that my query is not well formulated.
This is however my first attempt at using a Database of any kind so I would like to ask how I can speed up my query before I would make any large changes to my project.

Edit:
I solved my problem. @ypercubeᵀᴹ was indeed correct in that the solution was to index my columns that I was querying. SELECT statements are incredibly fast now by a huge margin, taking roughly 0.03 seconds. Thankyou.


texts: Can I create a user defined table type and use it in the same transaction?

texts:
When I execute the following (in management studio, GO will separate the commands into batches)

use tempdb

begin tran
go

CREATE TYPE dbo.IntIntSet AS TABLE(
    Value0 Int NOT NULL,
    Value1 Int NOT NULL
)
go

declare @myPK dbo.IntIntSet;
go

rollback

I get a deadlock error message. My process has deadlocked with itself. I've seen this behaviour in 2008, 2008R2 and 2012.

Is there a way to use my newly created type inside the same transaction it was created?


texts: How to interpret AlwaysOn event hadr_db_partner_set_sync_state

texts:
We have a program that listens to the SQL Server AlwaysOn_health event session
for state changes.  When we simulate a secondary server going offline or online, we get the hadr_db_partner_set_sync_state event from SQL Server.  (See below.) When I Google for "hadr_db_partner_set_sync_state sync_state" I get 6 hits and none explain the fields of this event.

{"EventName":"hadr_db_partner_set_sync_state",
 "Fields":
 {"database_id":"95",
 "commit_policy":"WaitForHarden",
 "commit_policy_target":"WaitForHarden",
 "sync_state":"NOT",
 "sync_log_block":"28119150887390",
 "group_id":"070e4751-d4cc-4154-9ff5-d2723838d0e4",
 "replica_id":"8b1c6688-cd6d-4668-b90b-f235121c82b2",
 "ag_database_id":"16e2a407-e136-4755-85b4-9f576d542ed1"
 },
 "Actions":{}
}

Which combination of fields in this event tells us a secondary server has gone offline? And which tells us a server has been restored to online?

If there is some other event we should be watching for, that would be helpful also.


texts: ER_TRANSACTION_ROLLBACK_DURING_COMMIT MySQL error when querying

texts:
I've recently seen a high number of errors on my Bugsnag integration of my web app which occur when both reading or writing to my MySQL server.
ER_TRANSACTION_ROLLBACK_DURING_COMMIT: Plugin instructed the server to rollback the current transaction

For a bit of background on how my MySQL servers are setup:
I currently have a "cluster" of MySQL servers running on 5 VMs which are running the Group Replication plugin in multi-master mode with the following config:

I've searched for the MySQL error code ER_TRANSACTION_ROLLBACK_DURING_COMMIT online as well as on stackoverflow, and as far as I can see, I can not find anything that helps me find the cause of this issue or the resolution.
Unfortunately I don't have any more information other than this, but if you require anything specific to help me, please do ask.
Thanks, Dan.
Edit: Added Group Replication config


texts: How to concatenate psql variables in tablename?

texts:
I am trying to concatenate some fixed string with a variable to build a table name.  This simple example demonstrates the problem.  The variable "suffix" resolved properly, but "prefix" does not because I am unable to figure out how to tell psql that "ws" is not part of the variable name.

What is the proper syntax so that the resultant query selects from "my_ws_group" ?

localhost ~ > psql -v prefix=my -v suffix=group
psql (8.4.4.10, server 8.0.2)
WARNING: psql version 8.4, server version 8.0.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

dev=> select * from :prefix_ws_:suffix;
ERROR:  syntax error at or near ":"
LINE 1: select * from :prefix_ws_group;
                      ^
dev=>

I prefer to use psql 8.4 because that is most compatible with Redshift.


texts: Port SQL Server data to MySQL without Workbench

texts:
When using MySQL Workbench to migrate data from SQL Server to MySQL I ran into a bug that it won't escape apostrophes. In the interim I would like to still port that data over. Is there any simple way to do this?

I've exported my data into a file with INSERT statements but they don't match the syntax of MySQL (I'm new to MySQL) so I don't know if it would work, neither does the file contain semicolons at the end of each statement.

Thanks in advance.

Update

So, far it looks like the easiest way to do it is to export the data into flat files and go through each table and make sure to also generate a SSIS file that you can use to automate future exports. Like so:

 DTExec /File MyExportedSissFileForOneTable.dtsx

And then use PowerShell or some other scripting language to do all the different files you need to update. Then import the files with MySQL - which I haven't figured out yet.


texts: How to change the datatype of a column from integer to money?

texts:
I am attempting to convert a PostgreSQL table column from integer to money, but I am receiving the error:

  cannot cast type MyColumn to money

I have tried these two statements, but just haven't found any real example on how to really do it.

ALTER TABLE products
    ALTER COLUMN price TYPE money

and:

ALTER TABLE products
    ALTER COLUMN price TYPE money USING to_char(price, '999.99')

How can you change the datatype of a PostgreSQL column from integer to money?


texts: PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19)

texts:
Using Procedure Analyse() under MySQL 5.6.30 on a table with about 4 million rows makes the following recommendations:

change timestamp to char(19)
tinyint to ENUM  (I noticed it's overzealous with ENUMS in general)
change varchar to char(N)

I can see the point of changing varchar to char when appropriate, but the first two recommendations strike me as odd. Thoughts?


texts:
I have a datetime field, but I often query over the date part, like this

select * from table where date(datetime_field)=curdate()

This query can't use  index on datetime_field!
What would be the best solution with the minimum amount of changes in code or db?

PS. upgrading mysql could be an option if there is a solution I'm not aware of yet, but a more short term solution for 5.6 would be appreciated


texts: Is there a name for the class of characters represented by [] in SQL Server?

texts:
I am interested in the terminology for the characters you can put around table names, column names, etc. when referencing them in a query.  A name that in Microsoft SQL Server would be for []s and in MySQL would be for ``s or possibly ""s.

I am unable to find a generic term for them anywhere and am wondering if one exists.


texts: Why is sql server waiting for acknowledgment?

texts:
I have an application that often request a big set of rows [about 20K] from a sql view, in the mean time other applications can't update the table from where the data comes from, the update query's gets blocked by the select from the other application.
The wait code is "ASYNC_NETWORK_IO" which I found to be described as exactly whats happening by me, I quote:

This is usually where SQL Server is waiting for a client to finish
consuming data.   It could be that the client has asked for a very
large amount of data or just that it’s consuming it really slowly
because of poor programming.
Clients often process one row at a time, instead of caching the data on the client and
acknowledging to SQL Server immediately.

My question is why is sql server waiting for the client to finish processing that select job, before allowing updates to occur on the table? is that a setting or a configuration that I could change?


texts:
I have two tables of data, such as:

CREATE TEMPORARY TABLE data_1 (user int, data int);
CREATE TEMPORARY TABLE data_2 (data int, file varchar(2));
INSERT INTO data_1 VALUES (1, 2), (1, 2), (2, 3), (2, 4), (3, 5);
INSERT INTO data_2 VALUES (1, 'a'), (3, 'a'), (4, 'a'), (5, 'b');

Table 1
user data
1    1
1    2
2    3
2    4
3    5
3    6

Table 2
data file
1    'a'
3    'a'
4    'a'
5    'b'
6    'c'

First I want to know, which users have more than one data point? Easy:

> SELECT user FROM data_1 GROUP BY user HAVING COUNT(data) > 1;
+------+
| user |
+------+
|    1 |
|    2 |
+------+

I also want to know which data-points are referenced to from more than one file.

> SELECT data FROM data_2 GROUP BY file HAVING COUNT(file) > 1;
+------+
| data |
+------+
|    1 |
+------+

This has the problem that GROUP BY file means we only get one data point per file, but that can be solved by a GROUP_CONCAT if necessary.

Now, but difficulty is that I want to combine both the conditions into a single query.

I want to know which users have more than one data point, for data points that together show up in the same file.

So the expected output from the sample data above would be 2.
User 2 would match because it has two datapoints, 3 and 4, and both of those have 'a' for 'file' value.

User 1 wouldn't match, because only one of data 1 and 2 is present in table 2. User 3 also would go away, because even though both data points 5 and 6 are in table two, they have different file values.

I could combine my two queries from above like this:

SELECT user FROM data_1 WHERE data IN (
  SELECT GROUP_CONCAT(data SEPARATOR ',') FROM data_2 GROUP BY file HAVING COUNT(file) > 1
) GROUP BY user HAVING COUNT(data) > 1;

But I think that fails on several ponts. First it appears like the group_concat turns the data ids into a string like '3, 4', so the IN () comparison is now not done on the correct numbers. but even if the IN did what I wanted, I expect this would apply the results from the inner query "globally" in the WHERE clause. I would need the condition applied groupwise to the outer query.


texts: Creating row number for multiple columns?

texts:
I have the below data, and I want to only take one Location per Order ID ordered by the distance. This data set takes customer zips and compares to store zips and then returns the distance. I want to only choose the store they're closest to.
I have this so far:
SELECT
*
FROM (
    SELECT
    t.*,
    row_number() over(PARTITION BY orderid ORDER BY dts) rn
    FROM (
        SELECT
        location,
        orderid,
        group1,
        group2,
        group3,
        group4,
        group5,
        custid,
        dts,
        sum(qty) AS units,
        sum(bsk) AS demand
        FROM osfdist
        GROUP BY 1,2,3,4,5,6,7,8,9
    ) t
) a

But this just starts the increment at 1 for the first row ordered by Distane and then increments to the total number of rows. I want it to say:
Downtown    1
Downtown    1
Downtown    1
Downtown    1
Coastal     2
Coastal     2
Coastal     2
Coastal     2

etc, so I can select where row_number=1 and only select the Downtown records.

Location
OrderID
Group 1
Group 2
Group 3
Group 4
Group 5
Customer ID
Distance
Qty
Sales

Downtown
1
FOOTWEAR
SHOES
SHOES (LOW)
M
RUN
abc123
8.724497523
1
90

Downtown
1
APPAREL
PANTS
PANTS (1/1)
F
FOO
abc123
8.724497523
1
22.5

Downtown
1
FOOTWEAR
SHOES
SHOES (LOW)
U
ORI
abc123
8.724497523
1
55

Downtown
1
APPAREL
SHORTS
SHORTS
M
TRA
abc123
8.724497523
3
50

Downtown
1
APPAREL
PANTS
TRACK PANT
F
ORI
abc123
8.724497523
1
35

Downtown
1
APPAREL
PANTS
TRACK PANT
M
ORI
abc123
8.724497523
1
35

Downtown
1
FOOTWEAR
SHOES
SHOES (LOW)
M
ORI
abc123
8.724497523
1
65

Downtown
1
APPAREL
JACKETS
LIGHT JACKET
F
OUT
abc123
8.724497523
2
100

Downtown
1
APPAREL
PANTS
PANTS (1/1)
M
TRA
abc123
8.724497523
1
27.5

Downtown
1
FOOTWEAR
SANDALS/SLIPPERS
SLIDES
M
RUN
abc123
8.724497523
1
17.5

Downtown
1
APPAREL
TIGHTS
TIGHT LONG
F
TRA
abc123
8.724497523
2
35

Coastal
1
APPAREL
PANTS
TRACK PANT
F
ORI
abc123
8.888442956
1
35

Coastal
1
FOOTWEAR
SHOES
SHOES (LOW)
M
RUN
abc123
8.888442956
1
90

Coastal
1
FOOTWEAR
SANDALS/SLIPPERS
SLIDES
M
RUN
abc123
8.888442956
1
17.5

Coastal
1
FOOTWEAR
SHOES
SHOES (LOW)
U
ORI
abc123
8.888442956
1
55

Coastal
1
APPAREL
PANTS
TRACK PANT
M
ORI
abc123
8.888442956
1
35

Coastal
1
APPAREL
PANTS
PANTS (1/1)
M
TRA
abc123
8.888442956
1
27.5

Coastal
1
FOOTWEAR
SHOES
SHOES (LOW)
M
ORI
abc123
8.888442956
1
65

Coastal
1
APPAREL
JACKETS
LIGHT JACKET
F
OUT
abc123
8.888442956
2
100

Coastal
1
APPAREL
SHORTS
SHORTS
M
TRA
abc123
8.888442956
3
50

Coastal
1
APPAREL
PANTS
PANTS (1/1)
F
FOO
abc123
8.888442956
1
22.5

Coastal
1
APPAREL
TIGHTS
TIGHT LONG
F
TRA
abc123
8.888442956
2
35


texts: Server Local Connection Provider has stopped listening on local pipe due to a failure

texts:
All,

I'm looking at a database where every so often, connections to it will fail. The event viewer and SQL Logs have the following error in them:

  Server local connection provider has stopped listening on [
  \.\pipe\SQLLocal\MSSQLSERVER ] due to a failure. Error: 0xe8, state:
  4. The server will automatically attempt to re-establish listening.

I'm trying to determine the cause of this, as it's taking down the production database once or twice a day. Most of the time, it will reconnect within 30 seconds, but sometimes it can take 30+ minutes, and at that point, the customer restarts the SQL server, which gets it working again.

Here is what I've investigated so far:

Configuration Manager had all 3 protocols (shared memory, TCP/IP, named pipes) enabled for all options. We have since disabled named pipes.
sys.dm_exec_connections shows that 34 of the 45 connections to the DB right now are shared memory, so I don't think disabling named pipes did anything.
Shared memory is just named pipes on a local machine.
There are no additional events at the time of these errors in the event viewer. I've looked throughout Applications and Services logs as well as the Windows Logs, and nothing else is logged at the same time.
Connections work if we specify the hostname with tcp: in front of it. This seems to be an issue entirely with shared memory.
I've enabled trace flags 3689,4029 according to this post
Once the traceflags were enabled, I've seen additional entries in the event viewer like the following:
Network error code 0xe8 occurred while establishing a connection; the connection has been closed.  This may have been caused by client or server login timeout expiration.  Time spent during login: total 471 ms, enqueued 12 ms, network writes 0 ms, network reads 206 ms,  establishing SSL 252 ms, network reads during SSL 2 ms, network writes during SSL 0 ms, secure calls during SSL 0 ms,  enqueued during SSL 249 ms, negotiating SSPI 0 ms, network reads during SSPI 0 ms, network writes during SSPI 0 ms,  secure calls during SSPI 0 ms, enqueued during SSPI 0 ms, validating login 0 ms, including user-defined login processing 0 ms. [CLIENT: local machine]
The above error happened about 15 seconds before the connection stopped listening.
These errors are a lot more common when we have Sophos Endpoint protection enabled, but they still happened once when it was disabled.

SQL server @@version string:

Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) - 11.0.5388.0 (X64)
    Sep 23 2016 16:56:29
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

It's only SP2, so we may try upgrading the DB in case this got fixed.

Does anyone have ideas on where to go from here? I'm considering disabling Shared Memory, as that seems to be the culprit, but based on what I've read, I'm going to need to do a lot more research on that.


texts: Not able to create a new table new_table from t1 by comparing primary key values of another table t2 in MYSQL

texts:
I am trying to create a new table new_table from t1 by comparing primary key values of another table t2

ie,
      1) created table new_table

  2) gave the query

insert into new_table select a.* from t1 as a join t2 as b on a.primary_key=b.primary_key.

but after the execution i got a message ( 0 rows affected )

please specify the reason for this.


texts:
Is it possible to write a trigger when someone makes a change at the server level such as attaching or creating a database?  I have a dev server where anyone can add a copy of the product database for testing, but there is no cleanup procedure.  I want to add the following extended properties when a database is added.

EXEC sys.sp_addextendedproperty
@name = N'Owner',
@value = N'Username';

EXEC sys.sp_addextendedproperty
@name = N'StartDate',
@value = N'9/1/2011';


texts: SELECT UNION from two views of the same table


texts: Speeding up group by query in PostgreSQL 9.6.5

texts:
I have this table along with the respective indexes:

CREATE TABLE shares
(
    id BIGSERIAL NOT NULL PRIMARY KEY,
    poolid TEXT NOT NULL,
    blockheight BIGINT NOT NULL,
    difficulty DOUBLE PRECISION NOT NULL,
    networkdifficulty DOUBLE PRECISION NOT NULL,
    payoutinfo TEXT NULL,
    miner TEXT NOT NULL,
    worker TEXT NULL,
    useragent TEXT NULL,
    ipaddress TEXT NOT NULL,
    created TIMESTAMP NOT NULL
);

CREATE INDEX IDX_SHARES_POOL_BLOCK on shares(poolid, blockheight);
CREATE INDEX IDX_SHARES_POOL_MINER on shares(poolid, miner);
CREATE INDEX IDX_SHARES_POOL_CREATED ON shares(poolid, created);

... and this relatively simple query:

EXPLAIN (BUFFERS, ANALYZE) SELECT SUM(difficulty), miner, worker FROM shares
WHERE poolid = 'xmr1' AND created <= '12/18/2017' GROUP BY miner, worker;

... resulting in this execution plan:

 GroupAggregate  (cost=3276833.38..3354436.07 rows=2387929 width=60) (actual time=9241.633..11041.639 rows=342 loops=1)
   Group Key: miner, worker
   Buffers: shared hit=960 read=560857 dirtied=5837 written=7378, temp read=114684 written=114684
   ->  Sort  (cost=3276833.38..3290264.23 rows=5372340 width=60) (actual time=9234.860..10468.246 rows=2940014 loops=1)
         Sort Key: miner, worker
         Sort Method: external merge  Disk: 352784kB
         Buffers: shared hit=960 read=560857 dirtied=5837 written=7378, temp read=114684 written=114684
         ->  Bitmap Heap Scan on shares  (cost=411031.05..2272306.15 rows=5372340 width=60) (actual time=462.250..4411.353 rows=2940014 loops=1)
               Recheck Cond: ((poolid = 'xmr1'::text) AND (created <= '2017-12-18 00:00:00'::timestamp without time zone))
               Rows Removed by Index Recheck: 9129527
               Heap Blocks: exact=40162 lossy=500188
               Buffers: shared hit=960 read=560857 dirtied=5837 written=7378
               ->  Bitmap Index Scan on idx_shares_pool_created  (cost=0.00..409687.97 rows=5372340 width=0) (actual time=454.259..454.259 rows=2940014 loops=1)
                     Index Cond: ((poolid = 'xmr1'::text) AND (created <= '2017-12-18 00:00:00'::timestamp without time zone))
                     Buffers: shared hit=4 read=21463 written=356
 Planning time: 0.171 ms
 Execution time: 11081.661 ms
(17 rows)

Two questions:

Why did the planner chose to sort first and apply the filtering afterwards, causing the costly outer part of the plan?
What indexes could be created to speed up the query? I've experimented with a lot of multiple column indexes but none yielded any significant speed improvement


texts: Error using sp_send_dbmail with a @query parameter

texts:
This has been asked many times, and every time I've seen, the solution is to add the @execute_query_database parameter. Well, as you can see, I have that parameter and this code:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'GMail',
@recipients = 'brady@bradykelly.net',
@subject = 'Snappy Referrals', -- add date
@body = 'Hello, DB mail!',
@execute_query_database = 'SeniorExecs',
@query = N'SELECT * from Language'

Still gives the error below as long as the @query parameter is present. When I comment out that one line, the proc call works and the mail is sent.

Failed to initialize sqlcmd library with error number -2147467259.

When I examine the DB Mail logs, in the Sql Server section, everytime I try and execute the proc, an entry like this gets added:

  Unknown,Disallowing page allocations for database 'WideWorldImporters'
  due to insufficient memory in the resource pool 'default'.

At this point, the only thing to say about database WideWorldImporters is that it is on the same SQL server. I encountered it nowhere setting up a mail profile and user account.


texts: Ordering with custom paging when using pivot to convert a query

texts:
This is a follow up to my question on changing a flat result set into a different column set using Pivot.

Scripts for test DB can be found in that link

Ordering works just fine like this:

SELECT TOP 100 PERCENT *, ROW_NUMBER() OVER(ORDER BY ID) Corr
        FROM
        (
            select p.ID, firstname,[Date], e.Title as stringvalue
    from
            dbo.DayPart dp
         LEFT outer JOIN
            dbo.PersonDayPart idp ON dp.ID = idp.DayPartID
        left outer JOIN
            dbo.Person p ON idp.PersonID = p.ID
              LEFT outer JOIN
        dbo.Event e ON e.ID = idp.DayPartID

        ) as d
        pivot
        (
            min([stringvalue])
            for [Date] in
          ([01/01/12],[02/01/12],[03/01/12],[04/01/12],[05/01/12],[06/01/12],[07/01/12])
        )
        as p
        ORDER BY firstname desc

I have added custom paging to the query and that works fine.

I then wanted to add ordering to the query and I'm coming against the well known issue "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

I am aware there are issues with using TOP, however I have gave it a bash anyway.
When I try to do the order within my CTE, the order does not affect the query at all.

EDIT: having issue uploading the declare statements in bold, please remove the dot in each to get the script to run properly. Every time I try to edit those, my connection crashes.... totally bizarre

DECLARE @StartDate DATE = '20120101';
**D.ECLARE** @SortBy varchar(10) = 'FirstName'
        **D.ECLARE** @sortDir varchar(10) = 'desc'
    DECLARE @size int = 2
    DECLARE @page int = 1
SELECT TOP (7)
    @sql += N',[' + CONVERT(CHAR(10), DATEADD(DAY, ROW_NUMBER() OVER
      (ORDER BY [object_id])-1, @StartDate), 120) + ']'
    FROM sys.all_objects ORDER BY [object_id];

    SELECT @sql = N'
    WITH CTE AS
    (
        SELECT TOP 100 PERCENT *, ROW_NUMBER() OVER(ORDER BY ID) Corr
        FROM
        (
            select p.ID, firstname,[Date], e.Title as stringvalue
    from
            dbo.DayPart dp
         LEFT outer JOIN
            dbo.PersonDayPart idp ON dp.ID = idp.DayPartID
        left outer JOIN
            dbo.Person p ON idp.PersonID = p.ID
              LEFT outer JOIN
        dbo.Event e ON e.ID = idp.DayPartID

        ) as d
        pivot
        (
            min([stringvalue])
            for [Date] in
          (' + STUFF(@sql, 1, 1, '') + ')
        )
        as p
        ORDER BY ' + @sortBy + ' ' + @sortDir + '
    )
    SELECT *
    FROM CTE
    WHERE Corr BETWEEN ('+ CONVERT(varchar(2), @size) + ' * (' + CONVERT(varchar(3), @page) + ' - 1))+1 AND ('+ CONVERT(varchar(2), @size) + '*' + CONVERT(varchar(3), @page) + ')
    '

    print @sql
    EXEC sp_executesql @sql


texts: Indexing a date field on MongoDB - best practice

texts:
Hi We would like to index a date field on a potentially large collection.
Is indexing a date-time field a good practice? Or should we stick to a date field (e.g. 07/12/2017) indexing (both of the options are valid as candidates for our date field, of course the date time is more fit if there is no real impact on performance)


texts: Using an older MySQL binlog filename (with position zero)? Risks? Downsides?

texts:
Let's say I'm setting up a new slave. I start with a mysqldump after running mysql -e "STOP SLAVE", transfer that and restore it to the new server.

If I didn't capture the BINLOG filename and position after the STOP SLAVE and before running the mysqldump then what happens if I simply use a previous/older binlog filename (verified to be present on the master and older than the backup), with an position of zero?

It seems to just force the new master to replay all of the events to the new slave, obviously including a number of INSERTs, UPDATEs, and DELETEs which were already represented in the backup (those which happened during the overlapping time period).

Is there anything wrong or risky about that? Is there a downside other than the extra wasted time and CPU cycles (replaying perhaps a day's worth of overlapping transactions)?


texts:
I've searched the web for a little while and haven't found an answer I'm confortable with. I am testing a SQL Server on a VM that was presented new disc.

If disk latency is reduced, shouldn't throughput increase?  Is there a direct correlation between disk latency and throughtput?  Seems like if I have faster disk I should see an increase in throughput.


texts: Restoring Postgres fixtures into a different database with different premissions

texts:
I've got an application called BigSense that uses two database users/rolls. One has full permissions and is used by my application to create/adjust table definitions. Then the application starts with the full permission user during startup for database migrations and then drops down to the regular user for operations.

I also have a virtual environment system called vSense that can create VMs for the application in Vagrant and provision them using Ansible. For the MySQL version, the permissions are done in the initial schema:

CREATE DATABASE {{ database.name }};
GRANT ALL ON {{ database.name }}.* TO '{{ database.ddl_username }}'@'{{ servers.bigsense.hostname }}' IDENTIFIED BY '{{ database.ddl_password }}';
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON {{ database.name }}.* TO '{{ database.username }}'@'{{ servers.bigsense.hostname }}' IDENTIFIED BY '{{ database.password }}';

USE {{ database.name }};
CREATE TABLE ddl_info( id INT auto_increment PRIMARY KEY, version INT, installed TIMESTAMP );

So doing a mysql dump/restore to another database/username is fairly trivial (the initialization above is done prior to loading the fixture file).

The issue comes with attempting the same thing with postgres. I use the pg_dump/pg_restore functionality with -Fc for the formatting option. Here is the initial schema before the fixtures are loaded:

CREATE USER {{ database.ddl_username }} CREATEDB PASSWORD '{{ database.ddl_password }}';
CREATE USER {{ database.username }} PASSWORD '{{ database.password }}';

CREATE DATABASE {{ database.name }}
  WITH OWNER = {{ database.ddl_username }}
       ENCODING = 'UTF8';

GRANT ALL ON DATABASE {{ database.name }} to {{ database.username }};

\connect {{ database.name }};

CREATE EXTENSION postgis;
CREATE TABLE ddl_info( id SERIAL, version INT, installed TIMESTAMP );

GRANT SELECT,INSERT,UPDATE,DELETE ON ddl_info TO {{ database.ddl_username }};
GRANT USAGE ON SEQUENCE ddl_info_id_seq TO {{ database.ddl_username }};

In both these situations, the database.name/ddl_username/ddl_password/username/password are replaced with the settings for that environment (contained in an external settings file). The MySQL fixture restore works fine because permissions are set initially for the entire database where as postgres is more fine grained for each individual table.

When I attempt to restore the postgres fixtures I get lots of complaints because it attempts to restore the database with the original database roles (which don't exist / now have different names)

TASK: [database | install fixtures (pgsql)] ***********************************
failed: [database] => {"changed": true, "cmd": "bzcat /opt/bigsense/gls-data.postgres.bz2 | pg_restore -O -c -Fc -d bigsense", "delta": "0:00:54.358900", "end": "2015-04-28 11:53:29.027809", "rc": 1, "start": "2015-04-28 11:52:34.668909", "warnings": []}
stderr: pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3156; 2606 32324 FK CONSTRAINT fk_sensors_type_id db_gls_ddl
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.sensors" does not exist
    Command was: ALTER TABLE ONLY public.sensors DROP CONSTRAINT fk_sensors_type_id;

I've done a lot of searching and have tried replacing -Fc with various other formatting types, used --no-acls and a variety of other options, but I can't seem to get my postgres fixtures to load into a different database with different roles/users.

I realize the fact that I have two different users complicates this quite a bit (otherwise I should be able to simply load a dump with the user I wish to have permission over the database).

Is there an easy way to dump/restore this database with different users/roles mapped to the various permissions I need?


texts: MYSQL REGEXP for text not between parentheses

texts:
I'm looking to search a large MYSQL table for a string and only return results that aren't between parentheses. If my table looks like this:

|  ID   |Data                      |
|   1   |Some search term here     |
|   2   |Search term               |
|   3   |Search term (search term) |
|   4   |(Search term) search term |
|   5   |Here's the (search term)  |
|   6   |Never match this line     |

And I want to select the rows where "search term" (case-insensitive) appears, but not in parentheses, how can I write the REGEXP to achieve that?

IDs 1,2,3,4 should return. IDs 5 & 6 shouldn't.

The search term may include letters, numbers and punctuation (though it shouldn't need to support parentheses as they'll never be nested).


texts: Proper storage size estimation technique for PostgreSQL

texts:
We are preparing a PostgreSQL database for production usage and we need to estimate the storage size for this database. We are a team of developers with low expertise on database administration, so we are doing research, reading manuals and using our general information technology knowledge to achieve this.

We have actual data to migrate to this database and have some rough estimations of growth. For the sake of the example, let's say we have a estimation of growth of 50% per year.

The point is: what's the general proper technique for doing a good size estimation?

We are estimating the storage usage by the following rules. Topics where we need advice are marked with bold text. Feedback on the whole process is more than welcome:

Estimate the size of each table

Discover the actual size of each row.

For fields with a fixed size (like bigint, char, etc) we used the sizes described in the documentation
For fields with a dynamic size (like text) we estimated the string length and used the function select pg_column_size('expected text here'::text)
We added 4 more bytes for the OID that PostgreSQL uses internally

Multiply the size of each row by the number of estimated rows
Do I need to consider any overhead here, like row or table metadata?

Estimate the size of each table index

Don't know how to estimate this, need advice here

Estimate the size of the transaction log

Don't know how to estimate this, need advice here

Estimate the size of the backups (full and incremental)

Don't know how to estimate this, need advice here

Sum all the estimates for the actual minimum size
Apply a factor of 1.5x (the 50% growth) to the sum of the estimates 1, 2 and 4 for the minimum size after 1 year
Apply an overall factor of 1.2 ~ 1.4 (20% to 40% more) to estimates 5 and 6 for a good safety margin

I know the rules got pretty extensive. Let me know if examples are necessary for a better understanding.


texts:
I'm using a PHP web application to interact with a database and need to catch an erroneous query. It's dynamically generated SQL and I don't know 100% of the code nor did I write it so I need to see what it's doing.

Using SQL Server Management Studio 2008 I've found it amazingly hard to find the last n executed queries on the database. I've searched up a storm but all solutions I've seen require "sql_handle" which is a feature not availible in my database's compatibility level, and to boot all solutions I've seen don't appear to catch dynamic queries, which this web application uses.

I was using this query:

SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
  execution_count,s2.objectid,
    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
  THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
       last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
ORDER BY last_execution_time DESC

But it unacceptably requires changing sp_dbcmptlevel in SQL Server 2005 (the search requires comptlevel at 90, I need it at 80), and in a recent case doesn't even return any dynamic queries run on the database; it's only returning the actions of stored procedures.

A similar solution with the same problems of compatability and incomplete results is:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

This one returns a very small and very odd subset of queries, including the most recent query (usually itself) and a bunch of create procedure statements.

Is there any feature of SSMS or system database query that will allow me to view or capture recent queries? If I have to capture and save the query strings somehow that's acceptable as this is only in a test system.


texts: SELECTing multiple columns through a subquery

texts:
I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them.

SELECT
  DISTINCT petid,
  userid,
  (SELECT MAX(comDate) FROM comments WHERE petid=pet.id) AS lastComDate,
  (SELECT userid FROM comments WHERE petid=pet.id ORDER BY id DESC LIMIT 1) AS lastPosterID
FROM
  pet LEFT JOIN comments ON pet.id = comments.petid
WHERE
  userid='ABC'      AND
  deviceID!='ABC'   AND
  comDate>=DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 MONTH);

Basically, I am trying to get the lastComDate & lastPosterID from the same row - the row which is the latest one in comments for the specific pet. Please suggest how can I get them in an efficient way.

The above query works, but seems overkill as same row is fetched twice. Moreover, the ORDER BY clause is significantly slower than the aggregate function - as I found while profiling query. So, a solution avoiding sorting would be appreciated.


texts: copy data from public schema to another schema in postgresql

texts:
I'm creating a multi-tenant RoR app using PostgreSQL 9.3.5 running on linux (Ubuntu 14). I'd like to copy the data from my public schema to another schema.

I tried:

pg_dump -U my_username -h localhost --schema=public -d my_db -Fc -f db/my_dump.backup

pg_restore -U my_username -h localhost --schema=my_schema -d my_db -a db/my_dump.backup

It does not copy the data from the public schema. Any thoughts?


texts:
MariaDB has crashed several times in the last week.

The following is an excerpt from the log immediately following the last crash.

How can I troubleshoot this to hopefully avoid future crashes?

I included two lines from about two hours before the crash as they might be relevant. I do not know why these name resolution errors are appearing.

They seem to be hack attempts, as the listed IP address is in Hong Kong.

2018-03-08 17:59:36 140300723444480 [Warning] IP address '164.52.6.148' could not be resolved: Temporary failure in name resolution
2018-03-08 17:59:36 140300723444480 [Warning] IP address '164.52.6.148' could not be resolved: Temporary failure in name resolution
180308 19:10:46 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 10.1.16-MariaDB
key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=21
max_threads=153
thread_count=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467113 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0x7f9a297fa008
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f9a4eccf140 thread_stack 0x48400
/usr/sbin/mysqld(my_print_stacktrace+0x2b)[0x7f9a52d35aab]
/usr/sbin/mysqld(handle_fatal_signal+0x475)[0x7f9a52892ef5]
/lib64/libpthread.so.0(+0xf7e0)[0x7f9a51e987e0]
/lib64/libresolv.so.2(__libc_res_nquery+0x1e8)[0x7f9a4eef8858]
/lib64/libresolv.so.2(+0x8dd0)[0x7f9a4eef8dd0]
/lib64/libresolv.so.2(__libc_res_nsearch+0x1e8)[0x7f9a4eef98f8]
/lib64/libnss_dns.so.2(_nss_dns_gethostbyname4_r+0xf7)[0x7f9a19dec7d7]
/lib64/libc.so.6(+0xcff03)[0x7f9a5035bf03]
/lib64/libc.so.6(getaddrinfo+0x150)[0x7f9a5035ef30]
/usr/sbin/mysqld(_Z14ip_to_hostnameP16sockaddr_storagePKcPPcPj+0x48d)[0x7f9a5289f5ad]
/usr/sbin/mysqld(+0x51dbf8)[0x7f9a527e5bf8]
/usr/sbin/mysqld(_Z16login_connectionP3THD+0x45)[0x7f9a527e6dc5]
/usr/sbin/mysqld(_Z22thd_prepare_connectionP3THD+0x19)[0x7f9a527e7579]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x158)[0x7f9a527e7728]
/usr/sbin/mysqld(handle_one_connection+0x47)[0x7f9a527e78a7]
/lib64/libpthread.so.0(+0x7aa1)[0x7f9a51e90aa1]
/lib64/libc.so.6(clone+0x6d)[0x7f9a50374aad]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x0): is an invalid pointer
Connection ID (thread ID): 933190
Status: NOT_KILLED

Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=off

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

We think the query pointer is invalid, but we will try to print it anyway.
Query:


texts: Oracle: calling udf that modifies the data. How to do it right?

texts:
Say I have a function :

create or replace
function test_function return integer
as PRAGMA AUTONOMOUS_TRANSACTION;
t_test integer :=1;
begin
 update test1 set id = id+1 ; // table test1 has, for simplicity sake, just 1 column,id
 commit;
 return (t_test);
end;

There are at least two ways I can execute it (so function successfully updates test1) :
1. SELECT test_function FROM dual;
2. SELECT 1 FROM dual WHERE test_fucntion =1;

Which one is the right ?

Also, I can do
3. SELECT 1 FROM dual
   INNER JOIN (SELECT test_function FROM dual)a ON (1=1)
which doesn't show an error, but also doesn't update the table.

I'm really curious if it's intended behavior; I don't see why update fails...
I'm using Oracle 10 if it matters.

Thanks.


texts: Can I not add VIEW SERVER STATE to a role?

texts:
My server admin asked me to grant a user SA privileges for the purpose of upgrading our vCenter environment from 5.5 to 6.x and I refused. So I found a VMWare article that outlined all of the required permissions and set the up in a role so that I could assign the vCenter database user to the role for the purpose of upgrading and then remove the user from the role once complete. That part worked correctly with some cosmetic name changes to match our current environment. However the upgrade still failed with the following error:

 Error : The user associated with the DSN has insufficient privileges.
 Resolution: Provide sufficient privileges for the user by granting the user the following permissions:
 * VIEW SERVER STATE
 * VIEW ANY DEFINITION

OK so these are server level permissions. That is fine. So I wanted to add these privileges to the role I created for this "vc_admin".

USE master
GRANT VIEW SERVER STATE to vc_admin
Grant VIEW ANY definition to vc_admin
GO

Which netted me this set of errors.

Msg 15151, Level 16, State 1, Line 3
Cannot find the login 'vc_admin', because it does not exist or you do not eave permission.
Msg 15151, Level 16, State 1, Line 4
Cannot find the login 'vc_admin', because it does not exist or you do not have permission.

So I add the role to the master database CREATE ROLE vc_admin successfully. The code above is run again and I get the same errors again.

If I change the code snippet above to target the user instead of the role it completes successfully. I suspect my issue is a simple misconception.

I read that I might have to create a server role for this to work which would explain the error. I tried the following which SSMS thinks is incomplete / syntactically incorrect.

create server role vc_admin

Now according to msdn the authorization is optional and adding that didn't change the error. Expecting ID, Quoted ID or TO

I read now that ...

  Server-scoped roles were introduced on SQL Server 2012, they are not supported in SQL Server 2008R2.

So I that train of thought might be a goose chase for me.

How can I get these privileges assigned to my vc_admin role instead?


texts:
I am trying to validate if some text, which is json, before processing it further.

I am using the ISJSON function in SQL Server 2016.

I have a problem that some of my json is slightly malformed; it contains unescaped newline char, quotes and tabs. (I may also get some trailing commas as well, but these are less of a concern).

Json libraries in languages like C# are more forgiving and just transform/ignore these small errors.

Is there a way to allow ISJSON() to be less strict and accept unescaped newline, quote and tab chars?

Or

Is there a way in SQL to check if some text is valid json; which is forgiving of unescaped newline, quotes and tab chars?

Examples:

'[{"key1": "Some value",}]'    -- Trailing comma

'[{"key1": "Some \value"}]'    -- Unescaped backslash

'[{"key1": "Some   value"}]'   -- Unescaped tab

'[{"key1": "Some
value"}]'                       -- Unescaped new line


texts: MySQL user permission on stored procedure

texts:
I've created a simple stored procedure:

mysql> CREATE FUNCTION hello (s CHAR(20))
    -> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

But failed to run it:

mysql> SELECT hello('world');
ERROR 1370 (42000): execute command denied to user ''@'localhost' for routine 'test.hello'

Is it possible that my user name is an empty string? How do I create users and grant privileges? Can I grant a user all the privileges on all entities within a database?


texts: Create column for values between a range sql

texts:
What I want to achieve is to create a new column based on the num_extents between a range and only print the table name below.

This is what I have [table1]:

This is what I want [table2]:

I used a case statement that allows to get values between a range using the below:

select sum(case when num_of_extents between 0 and 9 then 1 else 0 end) as one,
       sum(case when num_of_extents between 10 and 19 then 1 else 0 end) as two,
       sum(case when num_of_extents between 20 and 29 then 1 else 0 end) as three,
       sum(case when num_of_extents between 30 and 39 then 1 else 0 end) as four,
       sum(case when num_of_extents between 40 and 49 then 1 else 0 end) as five,
       sum(case when num_of_extents >= 50 then 1 else 0 end) as six
from (
select
a.tabname,
count(*) num_of_extents,
sum( pe_size ) total_size_pg,
round(sum( pe_size*4),2) total_size_kb,
round(sum( pe_size*4/1024 ),2) total_size_mb,
round(sum( pe_size *4/1024/1024),2) total_size_gb
from systabnames a, sysptnext b, sysdatabases c
where a.partnum = b.pe_partnum
and a.dbsname = c.name
and a.dbsname = 'system'
-- a.tabname not like ' %'
group by 1
order by 3 desc, 4 desc
) a

Output from above:

one    2
two    1
three  1
four   5
five   0
six    2

Please ignore the naming of the columns used, but instead of doing a sum between the range, I want to print out the names of the tables instead like in [table2]. How can I change my sql case statement to do this in informix?


texts: I have mostly identical tables, however there are slightly differences like missing columns in older ones. I want to merge 5 tables into one. Is there any easy way to do this?


texts: Poor SQL Server performance when management studio closed

texts:
I've noticed a strange behaviour of SQL Server 2008 r2 express:

usually my query execution time is ~650ms, but if I open Management Studio and run any simple query (for example SELECT * FROM Something), the execution time becomes ~40ms. If I close management studio this value returns to normal ~650ms

And no matter where the query was executed: from Management Studio or my application (it uses ADO.Net)

ARITHABORT setting has no effect

Why is this happening?


texts: Circular replication with servers in different locations

texts:
My service runs on single machine, and database (mysql 5.1.66) is replicated to 2 other identical slaves (with lesser system resources, mysql 5.1.66) via chained replication. Machines are located in different isps. There will always be a single entry point to service, and if master goes down, i'll switch to the next slave and continue.

simply the scenario is as master -> slave1 -> slave2.

But i don't want to deal with resyncing the master, when it comes up. I'm thinking of switching to circular replication model. which is:

master -> slave1 -> slave2 -> master ...

I've never used circular model, and the latency between master and slaves do not bother much in current implementation. But with circular model, i guess master will also be waiting for a reply from slave2, even though no nodes other than the master will be writing the database. Is this latency -if exists- a show stopper? Is it sane to implement circular replication between different locations?


texts: Configure SQL server to force encryption programmatically

texts:
I'd like to enable the ForceEncryption flag for SQL server programmatically.
I tried:
    Set-ItemProperty `
        -Path "Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib" `
        -Name ForceEncryption `
        -Value 1;

But that didn't seem to reject connections when they were unencrypted (I was still getting rows returned by SELECT session_id FROM sys.dm_exec_connections WHERE encrypt_option != 'TRUE').
I'm trying to get the configuration described here, where clients connect with the TrustServerCertificate option set to true (and as such encryption occurs using a self signed certificate).
I'm using SQL Server 2019.  The database is running on the same host as the code that is trying to connect to the database. The script is also running on that host. It is not a named instance.  I have restarted the SQL Server instance - with no effect.


texts: Clean up all but current backupset details

texts:
When backing up a Microsoft SQL Server database, even if I specify to backup to a new backup set and "Overwrite all existing backup sets", or even to a new media set, the msdb backupset/mediaset tables still retain the history of all previous backupsets. My questions are as follow:

What does it mean to "Overwrite all existing backup sets" if the history is preserved? Does it simply mean that it will delete other backupsets from the media?
How can I cleanup backup history for the database for all but current mediaset/backupset?
Related to 2) how do I select only "current" records from backupset/mediaset tables? I don't see any column indicating this. Does it mean I have to sort by date and somehow decide that this is the "current" one?


texts: Does anyone know how to execute a shell script from a PL/SQL procedure or function? Let's say the shell script is '/usr/bin/bash/convert.sh'.


texts: Select rows starting from specified value until another specified value

texts:
I have a table containing several columns and want to check for a column value, select the rows after that column value until a separate column value shows up. I have tried using BETWEEN, but if the column values are integers it only looks for numbers between the integers.

For example, if I have a table like this:

id  time      value
t1  12:00PM   15
t1  12:02PM   4
t1  12:03PM   7
t1  12:05PM   16
t5  12:10PM   250
t5  12:15PM   15
t8  11:00AM   15
t8  3:00PM    2
t8  3:05PM    100
t2  7:00PM    15
t2  7:01PM    16
t15 5:00AM    35

I would want to get the rows between values of 15 and 16. Basically, if I could sort by id, then time, and gather the rows after 15 appears until there is a value of 16 within that same id. If there is no value 16, I would want the next 100 rows for example and then search for the next value of 15.

I would like the query to return this:

id  time      value
t1  12:00PM   15
t1  12:02PM   4
t1  12:03PM   7
t1  12:05PM   16
t2  7:00PM    15
t2  7:01PM    16
t5  12:15PM   15
t8  11:00AM   15
t8  3:00PM    2
t8  3:05PM    100

That may be confusing. I've tried:

SELECT * FROM table WHERE value BETWEEN '15' AND '16' ORDER BY id, time

as a starting point, but that only returns rows with values of 15 or 16 because they are integers.

I want to sort the table by id then time. These entries are added automatically via another system so I'm trying to query the table for specific value ranges.

Any ideas?

Clarification:

If I had rows with 15, 1, 16, 7, 15, 2, 16 for the same id, I would want both "islands": 15, 1, 16, 15, 2, 16.


texts: 100% I/O utilization when mysqlcheck is running

texts:
The following mysqlcheck commands (run daily) are causing 100% Disk I/O utilization for a long period of time (1-2 hours):

mysqlcheck --check --auto-repair --all-databases;
mysqlcheck --optimize --all-databases

Which mysql server configuration settings can be used in order to prevent/limit high disk I/O?

posted the entire mysql configuration here: https://pastebin.com/huvkfkUF .
Thanks for any help!


texts: postgresql replication - pg_stat_replication is showing empty columns

texts:
I've a postgresql 9.2 streaming replication setup. It appears that the slave is getting the updates from master and is in sync. I've verified it by looking at pg_xlog dir and process list.

$ ps aux | grep 'postgres.*rec'
postgres 26349  2.3 42.9 38814656 18604176 ?   Ss   Sep20  24:06 postgres: startup process   recovering 000000010000026E00000073
postgres 26372  4.9  0.1 38959108 78880 ?      Ss   Sep20  51:27 postgres: wal receiver process   streaming 26E/731E05F0

And the startup logs on the slave also look alright.

2013-09-21 03:02:38 UTC LOG:  database system was shut down in recovery at 2013-09-21 03:02:32 UTC
2013-09-21 03:02:38 UTC LOG:  incomplete startup packet
2013-09-21 03:02:38 UTC FATAL:  the database system is starting up
2013-09-21 03:02:38 UTC LOG:  entering standby mode
2013-09-21 03:02:38 UTC LOG:  redo starts at 26E/71723BB8
2013-09-21 03:02:39 UTC FATAL:  the database system is starting up
2013-09-21 03:02:39 UTC LOG:  consistent recovery state reached at 26E/75059C90
2013-09-21 03:02:39 UTC LOG:  invalid xlog switch record at 26E/75059E98
2013-09-21 03:02:39 UTC LOG:  database system is ready to accept read only connections
2013-09-21 03:02:39 UTC LOG:  streaming replication successfully connected to primary

What worries me is that the pg_stat_replication table on the master. It shows that there is a client connected, but doesn't show anything more.

archive=> select * from pg_stat_replication;
 pid | usesysid |  usename   | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
-----+----------+------------+------------------+-------------+-----------------+-------------+---------------+-------+---------------+----------------+----------------+-----------------+---------------+------------
 999 |    16384 | replicator | walreceiver      |             |                 |             |               |       |               |                |                |                 |               |
(1 row)

Is this the expected behavior? I remember seeing values for client_addr, sent_location, replay_location etc. when I did a test run some time back. Is there anything that I'm missing?


texts: Alternatives to MySQL Partitioning, so we can have a Unique Index

texts:
I have a DBA vs Developer Issue here, and am wondering if there are any alternatives to partitioning that I can employ here.

I have a table as follows:

CREATE TABLE `tracking` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CustId` int(10) unsigned DEFAULT NULL,
  `SourceName` varchar(32) DEFAULT NULL,
  `SourceId` int(10) unsigned DEFAULT NULL,
  `Created` datetime NOT NULL,
  PRIMARY KEY (`Id`,`Created`),
  KEY `Cust_Source`(`CustId`,`SourceName`,`SourceId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( TO_DAYS(Created))
(PARTITION `201903` VALUES LESS THAN (737515) ENGINE = InnoDB,
 PARTITION `201904` VALUES LESS THAN (737545) ENGINE = InnoDB,
 PARTITION `201905` VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

As you can see, I like to partition my tables, as we only ever keep 2 months data in the live system, and this way I can automatically DROP the oldest partition each month.

However, my developer has asked if I can change the Cust_Source Index to be a UNIQUE Index, to prevent duplicate data being added by mistake. But so doing means I need to lose the partitioning. As:

"Error Code: 1503. A UNIQUE INDEX must include all columns in the table's partitioning function"

And of course, if I include the created field in the Index, we could get Duplicate data, created at different times.

Any suggestions?

This needs to work on MySQL 5.5 and 5.7 (although we'll be retiring 5.5 soon, hopefully).


texts:
I am POCing AlwaysON DAG for my company, and i have come into a very interesting debacle.
Seems like even though primary and replica's and all synced up, the log file on the primary DB does not get truncated automatically even with a checkpoint. It would have to wait for a log backup to be issued. Does it mean that even with AG, we still need to have scheduled TLOG backups running?


texts: computed column as foreign key allow null

texts:
I have two tables.

TABLE A:

One of them stores some integer identificators, which are unique, but actually are structured entities. I will call them full_id.

Actually its a two parted indetificator, first byte of which is type of identificator. Lets call it type_id It can have values, which are divided in two groups:

group named '0x0f group': [0x01, 0x02, 0x03, 0x04].
group named '0xf0 group': [0x10, 0x20].

The last 3 bytes of full_id is arbitraty number and goes by name actual_id

So, it given that the full_ids are unique.
acutual_ids are also unique, but only in scope of 0x0f group of type_id values: there cant be equal actual_id coupled with any of those type_ids.

On the other hand - actual_ids are not unique in a scope of 0xf0 group of type_ids values and in practice there is plenty of duplicated values of actual_id coupled with those type_ids

in SQL table A goes like so:

CREATE TABLE A (
    full_id INTEGER NOT NULL PRIMARY KEY,
    actual_id  AS (
        CASE
            WHEN full_id & 0x0F000000 <> 0 THEN
                full_id & 0x00FFFFFF
            ELSE
                NULL
        END
    ) PERSISTED
)

TABLE B:

The second table contains only actual_ids which is supposed to have type_id within 0x0f group. This is caused by the data stream, from which the second table is populated and this is something I cant change.

in SQL table B goes like so:

CREATE TABLE B (
    actual_id INTEGER NOT NULL
)

I want to:

I want to apply a foreign key:

ALTER TABLE B WITH CHECK
ADD CONSTRAINT fk_a_actual_id FOREIGN KEY(actual_id) REFERENCES A(actual_id)

But I cant, because actual_id in table A must have unique constraint. And I cant apply unique constraint on calculated column. How can I overcome this situation?

My DBMS is Microsoft SQL Server 2012


texts: SQL Server - Bulk load blocking queries on heaps

texts:
SQL Server books online say in SET TRANSACTION ISOLATION LEVEL:

"Optimized bulk load operations on heaps block queries that are running under the following isolation levels:
SNAPSHOT
READ UNCOMMITTED
READ COMMITTED using row versioning".

What does optimized bulk load mean here exactly?


texts: Copying CSV file to temp table with dynamic number of columns?

texts:
I am wondering if there is a way to copy a csv file into a temp table where the number of columns is unknown in the csv file. The DB software I'm using is PgAdmin III. I figured out that if I do know the number of columns then I can create a temp table with that amount of columns and then copy the csv file in like so:

   CREATE TEMPORARY TABLE temp
   (
      col1 VARCHAR(80),
      col2 VARCHAR(80),
       ....
      coln VARCHAR(80)
   );

COPY temp FROM 'C:/Users/postgres/Boost.txt' CSV HEADER DELIMITER E'    '

However if I try to just simply copy the csv file to a temp table with no columns in the temp table, Postgresql (version 8.4) complains that I'm working with a table that has less columns than in the csv file. I've been researching and can't seem to find anything in the Postgresql docs about this. Does anyone know if copying a csv file into a temp table with an arbitrary number of columns decided at run time is possible in Postgresql? Once the temp table is loaded with the csv file I plan on doing some comparisons to other tables with the temp table before it is destroyed. Also the first row in the csv file contains headers.


texts: Best index for column where values are mostly the same

texts:
We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to efficiently pull out rows containing any one of these identifiers.

Given the value will be 0 or 1 say (I don't have figures yet) 99% of the time, how might it best be indexed to query against the minority case? Am I even right in thinking the volume of common values will be an issue?

           Column           |  Type   |     Modifiers
----------------------------+---------+--------------------
 event_value                | integer | not null

There are currently no indexes on this column. And I don't envisage the need to regularly select just the 0 or 1 values.

The table is of a reasonable size, currently 30 million rows and growing fast.

I appreciate this isn't the best use of the column, but that can't change in the short term.


texts: SQL Server 2008 R2 - Weekly / Monthly Statistics

texts:
I'm new working with SQL Server 2008 R2 Express, and I´d like to gather some statistics every week / month for users who are connecting to this server.  For instance, how many times they were connected, which table was most popular or when was the most traffic, etc.

I cannot find anything about this weekly / monthly user statistic for SQL Server 2008 R2.

Do you know how to do that?


texts: SQL Server changes in tables reverted after two three days in two different DBs

texts:
I'm having a strange issue in one SQL Server 2008 R2 I admin. Three days ago, a major renaming on the columns of a table was made due to a development. After the columns where renamed, the change was manually propagated to any DB dependencies and after that to the program making use of the server. Yesterday evening I was using the program and everything was working correctly. I even made inserts on the table using the new program interface.

Today, the boss was ranting because some important report (sp that depends on this table) was throwing an error. The report was changed to use the new column names, but when I got to the table, it was reverted to it's previous state (!!!) not only on one DB, but also on a 2nd one they have for another company (!!!!) The procedures still are correct (new development) and even the data I inserted after the change is in the table.

It never happened to me in other server. But I can remember just some months ago something similar happened. I'm really scared.

What would make a server revert table design changes in two different DBs after three days of normally working and several backups made?


texts:
I have 36 data of box with weights:

24.8
24.7
24.6
24.6
24.65
24.75
25.1
24.7
24.7
24.9
24.8
24.65
24.85
24.85
24.7
24.75
25
24.6
24.55
24.6
24.5
24.55
24.5
24.85
24.7
24.65
24.4
24.4
24.55
24.45
24.35
24.95
24.65
24.85
24.75
25

Which combination of 12 boxes to get results of 296.35? Can I can provide this with stored procedure or cross join in MySQL?

The query to insert are:

insert tbl(weight) values(24.8);
insert tbl(weight) values(24.7);
insert tbl(weight) values(24.6);
insert tbl(weight) values(24.6);
insert tbl(weight) values(24.65);
insert tbl(weight) values(24.75);
insert tbl(weight) values(25.1);
insert tbl(weight) values(24.7);
insert tbl(weight) values(24.7);
insert tbl(weight) values(24.9);
insert tbl(weight) values(24.8);
insert tbl(weight) values(24.65);
insert tbl(weight) values(24.85);
insert tbl(weight) values(24.85);
insert tbl(weight) values(24.7);
insert tbl(weight) values(24.75);
insert tbl(weight) values(25);
insert tbl(weight) values(24.6);
insert tbl(weight) values(24.55);
insert tbl(weight) values(24.6);
insert tbl(weight) values(24.5);
insert tbl(weight) values(24.55);
insert tbl(weight) values(24.5);
insert tbl(weight) values(24.85);
insert tbl(weight) values(24.7);
insert tbl(weight) values(24.65);
insert tbl(weight) values(24.4);
insert tbl(weight) values(24.4);
insert tbl(weight) values(24.55);
insert tbl(weight) values(24.45);
insert tbl(weight) values(24.35);
insert tbl(weight) values(24.95);
insert tbl(weight) values(24.65);
insert tbl(weight) values(24.85);
insert tbl(weight) values(24.75);
insert tbl(weight) values(25);


texts:
Consider a database with the following structure:

Tables

[dbo].[Customer], [dbo].[Address], and [dbo].[Report]

Schemas

[dbo] and [Reporting]

What I would like to be able to do is to have a principal (could be user or role) be able to manage views (CREATE, ALTER, DROP) in the Reporting schema only.  Further, this principal would need SELECT permission on all of the tables in the dbo schema (to support the creation of views in the Reporting schema).  The last thing I would like to be able to do is give INSERT, UPDATE, and DELETE permissions to the Report table only in the dbo schema.

Is this something that is possible?  I have yet to find the right combination of permissions.  I can use any combination of users, roles, etc. in order to accomplish this.


texts: Transaction level difference between using a large IN filter VS. "BEGIN TRAN/COMMIT"

texts:
I would like to delete about 100,000 records with minimal server overhead.  I've had a few nagging questions I haven't been able to test properly so I figured I'd ask some experts here.  Which would be better:

1-

BEGIN TRAN
DELETE FROM dbo.x
WHERE ID IN (
1
,2
,3
,4
...
,100000
)
COMMIT

2-

BEGIN TRAN
DELETE FROM dbo.x
WHERE ID = 1
GO
DELETE FROM dbo.x
WHERE ID = 2
GO
...
COMMIT

3-

DELETE FROM dbo.x
WHERE ID = 1
GO
DELETE FROM dbo.x
WHERE ID = 2
GO
...

My assumption is that #1 would cause a scan based on sample %, pick out the items, remove them, and log it as a series of transactions.  Perhaps it would base the info in the transaction log as to what pages were changed instead of each indvl. transaction, thus you can only rollback the entire activity and remark the pages, or you could commit. Is that correct?

On #2, I'm thinking does the GO statement cause a lot more transaction log activity by not allowing the SQL Server Storage Engine to roll all of these into 1 large transaction, but still provide some optimization for the transaction log with the BEGIN TRAN - COMMIT blocks, thus making it more effective than #3 but less effective than #1.

I then would assume #3 would be the worst one as each indvl. transaction is logged.

If anyone has any good blog posts or test scenarios they could point me to that would be great too.  I've looked at ways to dig deep in the transaction log to figure it out myself but at this point, I thought I would ask you guys.

Thanks!


texts: MySQL - Unable to Convert Primary Key to Auto Increment

texts:
I have a MySQL table where the Primary Key (int) was set by the software to be equal to the Primary Key (auto incremented) of the main table. Because of a change in design I now need the field to auto increment. When I tried to ALTER TABLE it came back with the following:-

ERROR 1062: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
SQL Statement:
ALTER TABLE `Clinic`.`ClientSupplementary` CHANGE COLUMN `idClientSupplementary` `idClientSupplementary` INT(11) NOT NULL AUTO_INCREMENT

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'ClientSupplementary' already exists

This has me totally puzzled as '1' is definitely unique as are all the other Primary Key values. Where should I be looking?


texts:
I am rather comfortable writing concrete schemas with explicit primary keys, foreign keys, indexes, views and stored procedures. For queuing I use a NoSQL solution called: Redis.

Recently I heard a web-application development talk where the presenter said he used MongoDB and Node.JS (server-side) with AngularJS on the client-side.

This significantly minimises data conversion; as JSON is used natively across the board.

Are the performance gains from data consistency enough of a reason to develop in NoSQL, rather than relational?

PS: Should note that MongoDB uses BSON, so that could be interchanges for something like CouchDB (correct me if I'm wrong, but that stores as plain JSON)


texts: Should DB_RECOVERY_FILE_DEST_SIZE be smaller than the respective partition size?

texts:
Is it OK to set the DB_RECOVERY_FILE_DEST_SIZE to be equal to a drive partition that is dedicated for this purpose?

That is, if I use a partition of 10GB, is it ok to set the DB_RECOVERY_FILE_DEST_SIZE to be equal to 10GB?

Or is there a chance that errors will occur due to insufficient space in the drive, and I should use a smaller value?

Edit (more details):

My setup has the following parameters:

db_recovery_file_dest            string  /oracle/fra
db_recovery_file_dest_size       big integer 10G


texts: Slow-running query returns incomplete resultset

texts:
So I'm trying to get some data from an SQL Server 2008 instance, the query is quite simple in structure but it has an extremely poor performance, it takes just under a minute to retrieve a bit over 1,000,000 rows (from SSMS).

Here you can see the execution plan.

The data is intended for statistical purposes and the query is being used from R-Studio (through RODBC), when querying from this environment its 'breaks' somewhere near the 450,000 - 500,000 rows. I figure RODBC is timing out somewhere near the 30 Sec mark...

The query

SELECT
    cc.ReportID,
    cc.ReportDate,
    cc.UnitID,
    cc.Callsign,
    cc.SpeciesCode,
    cc.ComName,
    cc.CatchWeight,
    cc.DayGrid,
    cc.NightGrid,
    cc.UnitEffortType,
    cc.UnitEffort,
    cc.Depth,
    cc.ReportType,
    cc.EffortDesc,
    cc.LicenceUsed,
    vn.ValidFrom,
    vn.Nation,
    vn.NationCode,
    vg.GRT
FROM
    vwVesselGRT vg
    INNER JOIN vwVesselNationality vn ON vg.UnitID = vn.UnitID
    INNER JOIN vwClientCatch cc ON vn.UnitID = cc.UnitID AND vn.ValidFrom <= cc.ReportDate AND vn.ValidTo > cc.ReportDate
WHERE
    cc.ReportDate <= '2016-04-30'

I was wondering if anyone has experienced such problem. Should I attempt to change the query or tackle the R side of the problem? I might not be spotting the real issue here, I'm afraid.


texts: SSIS Package Failing since Database failed over to Secondary (Basic Availability Group)

texts:
Our SSIs package running via SQL Agent Job on a dedicated Reporting server had been running fine since deployed more than 3 weeks ago. Due to performance issue we had to fail over the database to Secondary. Since we failed over the SQL Agent Job is failing with error in package indicating that the login failed for domain user (DOMAIN\etl.user.

We also noticed this error: The login is from an untrusted domain and cannot be used wit Integrated authentication.

Below is the visual of our Architecture and explanation:

More details:

Connection string is set at the SQL Agent job and the connection is made using  Listener.
The SQL Agent job is ran as Proxy user as we do not want the user to have SysAdmin privilege.
I have ensured that the domain login on secondary server exist.

Anyone has any idea why the package would fail?


texts: updating a field with a partial pass parameter

texts:
I am working on a table that has 24 columns named  pp1, pp2... pp24.

I would like to pass in a parameter that would be used to help identify the column to be updated.

one parameter is the value (8)
one parameter is the column (5)
one parameter is the rowID (256)

short stored proc...

update 'pp'+ [column_parameter]
set value to [value_parameter]
where rowID = [rowID_parameter]

Can the concatenation of the partial column name be combined with a parameter?


texts:
I'm having a deadlock issue that I don't get. Possibly it's because of the composite index (idSite,timestamp). Maybe it's range locking on it?

I have this table:

CREATE TABLE `logs` (
  `idLog` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `idSite` int(10) unsigned NOT NULL,
  `secondsToNextLog` int(11) DEFAULT NULL
  `gwRestarted` tinyint(4) NOT NULL
  `timestamp` int(10) unsigned DEFAULT NULL
  PRIMARY KEY (`idLog`),
  KEY `idSite` (`idSite`,`timestamp`)
) ENGINE=InnoDB

I already removed a foreign key constraint from idSite, because foreign key constraints can be confusing in deadlocks.

I have four processes inserting data into this table (from a queue). The show engine innodb status shows these queries deadlocking:

INSERT INTO logs(idSite, timestamp, secondsToNextLog, gwRestarted) values ('5396', '1438764388', '900', '0')

and

INSERT INTO logs(idSite, timestamp, secondsToNextLog, gwRestarted) values ('5395', '1438764417', '60', '0')

I don't see how this could be deadlocking. The table is very simple and the two queries both are for a different idSite (I made sure of that in the queue processors). Moreover, the engine status shows that both transaction only use and lock one table, so I don't see how this can be a deadlock.

Shouldn't it be easily possible to insert as many rows at the same time as one wants, as long as idSite is different?

This is the complete deadlock info:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-08-05 09:11:38 2b6fcbf25700
*** (1) TRANSACTION:
TRANSACTION 516854792, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 15503218, OS thread handle 0x2b6fcb273700, query id 3125430892 172.31.0.175 vrm_dev update
INSERT INTO logs(idSite, timestamp, secondsToNextLog, gwRestarted) values ('5396', '1438764388', '900', '0')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 135684 page no 15 n bits 776 index `idSite` of table `vrm_2012_dev`.`logs` trx id 516854792 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 516854776, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 2
MySQL thread id 15503217, OS thread handle 0x2b6fcbf25700, query id 3125430894 172.31.0.175 vrm_dev update
INSERT INTO logs(idSite, timestamp, secondsToNextLog, gwRestarted) values ('5395', '1438764417', '60', '0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 135684 page no 15 n bits 776 index `idSite` of table `vrm_2012_dev`.`logs` trx id 516854776 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 135684 page no 15 n bits 776 index `idSite` of table `vrm_2012_dev`.`logs` trx id 516854776 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)


texts: How can I safely rename a sequence in PostgreSQL, ideally without downtime?

texts:
We want to rename some sequences in our production database for certain reasons.

Is there a way to do this safely in production without having to first close all connections to the database?

It's fine if we get gaps in the ID sequence (e.g. it jumps from ID 123 to ID 200 or whatever) but we obviously don't want long locks causing delays/errors, don't want any risk of duplicate IDs and similar.

We're on PostgreSQL 9.5.21 on Heroku.

We've looked at the docs but are still uncertain about the consequences of running

ALTER SEQUENCE old_id_seq RENAME TO new_id_seq;
ALTER TABLE mytable ALTER COLUMN id SET DEFAULT nextval('new_id_seq');

in production. I guess one risk would be if stuff happens in mytable between those two commands. But what if we did something like this:

-- Starting at a much higher value than the currently highest ID.
CREATE SEQUENCE new_id_seq START 200;

ALTER TABLE mytable ALTER COLUMN id SET DEFAULT nextval('new_id_seq');

DROP SEQUENCE old_id_seq;

What are the risks of doing it that way?


texts: why binlog doesn't log the DML statement?

texts:
two method compared
1. use test; create table t (a int);
2. create table test.t1(a int);

my result is the first one can log the binlog but the second one can't not

use test;create table t(a int);

mysqlbinlog -v mysql-bin.000005|grep create
use `test`/*!*/;
SET TIMESTAMP=1490090893/*!*/;
create table t(a int)
/*!*/;
DELIMITER ;

but when I do not use "use", DML cannot log in binlog,can tell me why?

create table test.t(a int);

mysqlbinlog -v mysql-bin.000005|grep create test.t


texts: How to optimize search restricted by boolean column?

texts:
I want to optimize a query1 of the form

SELECT yid, xid FROM x WHERE is_principal;

Here, is_principal is a non-null boolean column of x.

If I run EXPLAIN QUERY PLAN on this query, the output I get is

0|0|0|SCAN TABLE x

I'd like to turn this SCAN into a SEARCH, so created the following index:

CREATE INDEX x_is_principal_idx ON x (is_principal);

...but it made no difference: the output of EXPLAIN QUERY PLAN remains unchanged.

Is there some other way to optimize this query?

1 FWIW, this query is in fact a sub-query of a much larger query that I'm trying to optimize.


texts:
I am trying to copy one database schema from one server to other. I know how to do it in same server using mysqldbcopy but i dont know how to user it while copying from one server to other.

mysqldbcopy --source=root:''@localhost --destination=root:''@localhost Test_DB:Test_DB_Copy

This is how i copy in the same server. Please help me out in copying the schema to different server using msqldbcopy command.


texts: SQL Server Log Reader Agent while trying to add node in p2p Replication

texts:
I am trying to add one more node in peer-to-peer cluster.
In current state, I have 3 servers A, B and C.
I need to add a node D with node A(only).
For this, I used following steps:

Took full backup from Node A
Copied it to Node D
Restored the backup
Added publications in both nodes.
Added subscription for Node D in publisher Node A with sync_type 'initialize with backup'

As soon as the commands were run, the Log Reader in Node A started throwing the below errors:

The process could not execute 'sp_MSadd_replcmds' on 'Node A'. (Source: MSSQLServer, Error number: 1007)
Get help: http://help/1007
The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor.
Retry the operation again with a more up-to-date log, differential, or full database backup. (Source: MSSQLServer, Error number: 1007)
Get help: http://help/1007
IDistPut Interface has been shut down. (Source: MSSQL_REPL, Error number: MSSQL_REPL2000)
Get help: http://help/MSSQL_REPL2000
The last step did not log any message! (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

As this was affecting the p2p replication between Nodes A, B and C, I removed the subscription for Node D.
This didn't resolve the error.
On further investigation I found that the Distribution Cleanup job was still running.
Probably that caused the issue.

Now, I am just hoping to make the p2p replication between already existing nodes A, B and C working again, the way it was before.
But, all my attempts have resulted in no change in Log Reader Agent.
It is stuck at the same error.
- The process could not execute 'sp_MSadd_replcmds' on 'Node A'. (Source: MSSQLServer, Error number: 1007)
Get help: http://help/1007
- The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor.
Retry the operation again with a more up-to-date log, differential, or full database backup. (Source: MSSQLServer, Error number: 1007)
Get help: http://help/1007
- IDistPut Interface has been shut down. (Source: MSSQL_REPL, Error number: MSSQL_REPL2000)
Get help: http://help/MSSQL_REPL2000
- The last step did not log any message! (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

Please advise on any steps to resolve this.
Thanks


texts: create index - invalid memory alloc request size

texts:
i get this error "invalid memory alloc request size 18446744072635840654. SQL Status:XX000" when i want to create an index.

The Table looks like this:

CREATE TABLE schema.tblname
(
  id bigint NOT NULL DEFAULT nextval(('schema.tblname_id_seq'::text)::regclass),
  column_1 integer,
  column_2 character varying(20),
  ... 8 other columns ...,
  CONSTRAINT tblname_pkey PRIMARY KEY (id),
  CONSTRAINT tlbname_fk FOREIGN KEY (column_1)
      REFERENCES schema.tblname2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

Server version:

PostgreSQL 8.3.8 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)

Hardware (Virtual Machine):

4 Cores, 16GB RAM, 700GB SAN HDD

ulimit -a:

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 139264
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 139264
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

The History:
We had a crash of the Raid-Controller in the SAN. The redundant Server/SAN prevented a failure of the DB but we had corrupted data in the table.
Then i have searched the table for corrupted data and deleted the rows from the table. Now i want to recreate the index to be sure to have a correct index.

I wanted to create following index:

CREATE INDEX tlbname_column1_column2x
  ON schema.tlbname
  USING btree
  (column_1, column_2);

This was not possible because the following error occurred:

index row requires 2286728 bytes, maximum size is 8191

So i made an index on column_1, this worked fine and deleted it after that.
Then i tried to create an index on column_2 and the error "index row requires 2286728 bytes, maximum size is 8191" occurred.
I searched the table for rows where the octet_length was greater then 20 (because of charvar 20 on column_2) and deletet the lines (approx. 40k rows).

Now when i try to create the index on column_2 it fails with the error "invalid memory alloc request size 18446744072635840654. SQL Status:XX000"

Table info:

rows in the table: aprrox. 1.841.170.000
tablesize: 197GB

The index

CREATE INDEX tlbname_column1_column2x
  ON schema.tlbname
  USING btree
  (column_1, column_2);

already exists since the creation of the table and it worked without any problems. But the recreation does not work.

Any suggestions what i can try or do?
Thanks.


texts: Coalesce on right hand table field with left join, or a better way?

texts:
I have two SQL Server 2014 tables one has "en-GB" cultured names and descriptions, and a second has translations for other cultures.

The "en-GB" table...

CREATE TABLE [dbo].[HomePages]
(
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Key]         NVARCHAR (25)  NOT NULL,
    [Name]        NVARCHAR (100) NOT NULL,
    [Description] NVARCHAR (256) NOT NULL,
    [Active]      BIT            CONSTRAINT [DF_HomePages_Active] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK.dbo.HomePage] PRIMARY KEY CLUSTERED ([Id] ASC)
);

The translation table:

CREATE TABLE [dbo].[HomePageTranslations]
(
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [HomePageId]  INT            NOT NULL,
    [CultureName] NVARCHAR (5)   NOT NULL,
    [Name]        NVARCHAR (100) NOT NULL,
    [Description] NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_HomePageTranslations] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_HomePageTranslations_Globalization] FOREIGN KEY ([CultureName]) REFERENCES [dbo].[Globalization] ([CultureName]) ON DELETE CASCADE,
    CONSTRAINT [FK_HomePageTranslations_HomePages] FOREIGN KEY ([HomePageId]) REFERENCES [dbo].[HomePages] ([Id]) ON DELETE CASCADE
);

There will always be values in en-GB table, but may or may not be equivalent values in the translation table. The query I am using should return the translated values if they exist or fall back on the en-GB values if they don't. The query is in the stored procedure here:

CREATE PROCEDURE [dbo].[HomePage_GetForKeyAndCulture]
(
    @Key NVARCHAR(25),
    @CultureName NVARCHAR(5)
)
AS
BEGIN
    SELECT      [base].[Id],
                [base].[Key],
                COALESCE(translation.[Name], base.[Name]) [Name],
                COALESCE(translation.[Description], base.[Description]) [Description],
                [base].[Active]

    FROM        [dbo].[HomePages]               [base]
    LEFT JOIN   [dbo].[HomePageTranslations]    [translation]
            ON  [translation].[HomePageId]  =   [base].[Id]
            AND [translation].[CultureName] =   @CultureName

    WHERE       [Key]                       =   @Key;
END

As you can see I am using a LEFT JOIN and the COALESCE function to achieve this. I know LEFT JOINS are not overly performant, so is there a more performant way to achieve this?

EDIT: Following advise tables have now had indexes added and look like...

The HOMEPAGE table...

CREATE TABLE [dbo].[HomePages]
(
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Key]         NVARCHAR (25)  NOT NULL,
    [Name]        NVARCHAR (100) NOT NULL,
    [Description] NVARCHAR (256) NOT NULL,
    [Active]      BIT            CONSTRAINT [DF_HomePages_Active] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK.dbo.HomePages] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE UNIQUE NONCLUSTERED INDEX [IX.HomePages_Key]
    ON [dbo].[HomePages]([Key] ASC);

And the TRANSLATION table...

CREATE TABLE [dbo].[HomePageTranslations]
(
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [HomePageId]  INT            NOT NULL,
    [CultureName] NVARCHAR (5)   NOT NULL,
    [Name]        NVARCHAR (100) NOT NULL,
    [Description] NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_HomePageTranslations] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_HomePageTranslations_Globalization] FOREIGN KEY ([CultureName]) REFERENCES [dbo].[Globalization] ([CultureName]) ON DELETE CASCADE,
    CONSTRAINT [FK_HomePageTranslations_HomePages] FOREIGN KEY ([HomePageId]) REFERENCES [dbo].[HomePages] ([Id]) ON DELETE CASCADE
);

CREATE UNIQUE NONCLUSTERED INDEX [IX_HomePageTranslations_HomePageIdCultureName]
    ON [dbo].[HomePageTranslations]([HomePageId] ASC, [CultureName] ASC);


texts:
When trying to disable change data capture on a 2014 instance EXEC sys.sp_cdc_disable_db, I get the message:

The result is a message like this:

  Transaction count after EXECUTE indicates a mismatching number of
  BEGIN and COMMIT statements. Previous count = 0, current count = 1

From my analysis, there were numerous objects that were removed (one job, four tables, data in the tables related to the change data monitoring), that should not have been removed.  I don't see how any recovery at this point is possible.  I don't see how else to remove all change data capture, other than drop every object one-by-one and I'm not convinced that would work.

If this occurs ("corruption of this whole process"), is there an alternative way to remove it?


texts: Export MySQL Database with version compatibility

texts:
I'm trying to move a MySQL database out of my Win 2012 server & onto a shared hosting web server (Linux).

I'm having problems importing the database (which imports fine into other hosted packages that I have).

The company that Im having issues with have asked if I'm running on version 4 of MySQL.

I've checked & the current version of MySQL on the original server is 5.6.13.  The destination server is 5.5.40 or 5.0.67 (not sure which is which from PHPMyAdmin interface.

Any ideas why I'd be experiencing errors & what command I need to include into the export command in MySQL to make the damn thing import into the other db?

Thanks


texts: Is there a way to show the creation statement for an index in PostgreSQL

texts: I need to recreate an index in PostgreSQL which has suffered index bloat. Since I need the index to be usable while it's being created, I can't use REINDEX. I am going to recreate the index with a new name and then drop the old one. Is there any way to see the SQL statement that was used to create an index so I can just copy that?


texts: Realtime Update MySQL on every Update occurred on PostgreSQL

texts:
I have a PostgreSQL for my accounting system and a MySQL database for my php website, i want to sync these two database for every update may occurred on PostgreSQL it also updates MySQL database, they are on linux operating system(ubuntu),

How can i do this work?


texts:
I have a coding using sqldeveloper under Oracle database using a student database, PowerSchool:

PS_CUSTOMFIELDS.getStudentsCF(s.id,'CA_LastIEPDate') AnnDate

This is a custom field, ca_lastIEPdate getting info from Students table and put in a column under AnnDate as AnnualDate and got 12-AUG-2018.  I tried to add to_Char( ,'MM/DD/YYYY') within the coding but could not make it out of it.


texts:
Is there any way to change physical path by database?

For example :

I have 2 databases in server.

database1 will be located in /data/database1/

database2 will be located in /data/database2/

Is this possible ?

thanks in advance.


texts:
I have 2 tables and I want to update table2 with criteria of table1 and table2.
The criteria are:

1.table1.amount > 10000

2.table2.flag is null or table2.flag = '0'

So if the 2 conditions are true, then update table2.flag = '1' and table2.flagdate = getdate()

table2.flag is a flag to check everyone who pays more than 10,000. But if already flagged, I don't want to flag again because I can't loose the original table2.flagdate. But I try a lot of things, nothing works the way I want. Any ideas?