texts:
I'm trying to join two tables and filter join results but I couldn't manage it and I'd appreciate any help.
I have these row sets:
Table A
id | userid | targetid | start_date
-----------------------------------
11 | user1  | 123      | 22/10/2019
22 | user1  | 123      | 02/10/2019
33 | user1  | 123      | 04/10/2019
44 | user1  | 456      | 02/10/2019
55 | user1  | 123      | 13/11/2020

Table B
id | targetid | start_date
---------------------------
66 | 123      | 21/10/2019
77 | 456      | 11/11/2020
88 | 123      | 11/11/2020
99 | 123      | 12/11/2020

What I'm trying to do is to find most recent Table B row for each Table A row using targetid as FK and using start_date as filter/order values. Here's the result I'm looking for:
11 | user1  | 123      | 66
22 | user1  | 123      | 66
33 | user1  | 123      | 66
44 | user1  | 456      | 77
55 | user1  | 123      | 99

I tried to inner join and limit join condition using on ... and a.start_date <= b.start_date but that made Table A | 55 join with each row in Table B.
This could be any type of SQL, join/cursor/loop the method and performance doesn't matter.
SQL Fiddle


texts: Table design - one table or hundreds of small ones

texts:
I have the following problem:

I have measuring devices, each one measure always the same quantities from the list of five different ones. Each device can measure different quantities from this list. I update the data only sometimes (once a month), but read them online based on time frames (no caching on app side).
Every device has aproximatelly 20K entries up to date.
New device will be added only in special cases, so devices can be seen as fixed.

My question is:

Should I have one big table or multiple (hunderds) of small ones.

Advantages / Disadvantages I see:

Single table

-) waste of space, since some columns are not filled, because device not measure certain quantity

+) safer select - device ID is not part of table name and can be passed safely with PDO or other library to prevent injections

+) easier to select multiple devices at once (however, this is not an usuall user case)

Multiple tables

+) "space" friendly

-) more complicated select - table name must be checked manually (from schema table) before select to prevent injections


texts: Is it possible to execute a select query and get the same result as at some point in the past for debugging an application?

texts:
I sometimes get bug-reports that our application returns wrong values. Wrong values means values which should have had other values at this point of time or contradicting values. As these values change over time and our application consists of multiple systems and users interacting with the same values (we have checks to prevent concurrency issues/race conditions), it is often complex to figure out the value(s) which were returned by the queries at a specific point of time. Currently we use the values at the time we get the bug-report and then use the log-files of our application to revert all changes since the time of the occurence of the "bug" in our head to estimate the values at the reported time. As you can guess this is very complicated/involves much work.

Currently rejected/deferred options:

get the latest backup before the incident and use db-logfiles for point-in-time recovery -> Because of the size of the database this would take multiple hours
log every result of select-queries in the application serverside -> This would get additional load to the server because most queries are select statements. Also I would like not to log some data for the same reason you don't log passwords in the log-file.

To give you an example:

some values inserted/updated/deleted/queried
10:00 user A uses our application which shows some "wrong" values or does not see them for some reason alltough another user says he inserted the data
some values inserted/updated/deleted/queried
11:00 we get bug-report from user A that the query was wrong. Now we want to make the same query to see which result the query initiated by user A at 11:00 should have returned/did return so that we know where to get started looking for the bug or if for example another user changed the value unexpected for user A or didn't have already inserted the data at that time

So is there some way to "look into the past"/see the result of a query in the past?

We use MS-SQL-server with recovery model "Full" and database mirroring.


texts: Pros and cons of DBCC SHRINKDATABASE operation

texts: I have read MSDN - Microsoft Manual according to this issue, but I still do not have clear understanding, when it is useful to apply this option and when it is not. Please advise.


texts: How does MS SQL Server authenticate MSDTC connections when using SSPI?

texts:
I'm trying to get SSPI authentication to play well with two-phase transactions via MSDTC, using PostgreSQL with psqlODBC. I can't figure out how it can work securely - so I'm hoping someone can explain how it works with MS SQL Server.

I'm coming from a PostgreSQL background, so please forgive any major misunderstandings of MS SQL.

The TL;DR is: To complete prepared transaction recovery after an app exits or the server crashes, MSDTC.exe running as NETWORKSERVICE has to be able to connect to the database and commit prepared transactions on behalf of the original user. When authentication was done over SSPI originally I don't understand how that's possible.

Background: SSPI, DTC, ODBC and XA

A user establishes one or more psqlODBC connections to PostgreSQL, and authenticates to PostgreSQL with SSPI. Windows confirms to PostgreSQL that the requested PostgreSQL username is the same as the current Windows username, and access is granted.

The user binds the connections into a DTC session with the MSDTC transaction manager and begins a distributed transaction (which begins a tx on the connections being managed by MSDTC). When they're done with their work on the connections they request a distributed commit on the DTC transaction. This does a two-phase commit - in PostgreSQL, calling PREPARE TRANSACTION on both as phase 1, then when phase 1 confirms success calling COMMIT PREPARED on each as phase 2.

Standard-ish stuff so far, just Microsoft's version of XA.

XA recovery

Part of the point of XA and MSDTC is that if the client crashes after phase 1 commit confirms, the transaction is guaranteed to get committed.

For that to happen something must keep track of outstanding prepared transactions and commit them. That's the job of the MSDTC.exe, the microsoft distributed transaction co-ordinator daemon.

At least with psqlODBC, when an abandoned prepared transaction is recovered, MSDTC loads the XA DLL for psqlODBC and calls xa_recover, which makes a connection to PostgreSQL using the credentials of the user who began the DTC transaction. It then calls COMMIT PREPARED and closes the connection.

MSDTC.exe runs as NETWORKSERVICE. That's the where the problem starts, because those stored credentials are not valid for NETWORKSERVICE if the original connection wasn't also by the user NETWORKSERVICE. SSPI will reject the connection because of a username mismatch.

How does MS SQL handle this?

MS SQL Server is heavily used with SSPI, and MSDTC is designed in part around the needs of MS SQL Server. So presumably, MS SQL Server has a secure way to complete distributed transaction recovery for a connection that was made using SSPI by a user other than NETWORKSERVICE even though msdtc.exe runs as NETWORKSERVICE.

I don't understand how it could, though.

The obvious options are all insecure, like:

Configure and store credentials in the registry, accessible by NETWORKSERVICE, so msdtc.exe can commit/rollback any user's prepared transactions;
Trust NETWORKSERVICE to connect to MS-SQL without credentials for the purpose of prepared transaction management;

and in the end, I don't see how MS SQL can prevent other services running as NETWORKSERVICE from messing with its prepared transactions.

Is this just one of those things where people accept somewhat weaker security? You're expected to reasonably trust everything running as NETWORKSERVICE and MS SQL just allows MSDTC to connect without additional checks?

Or is there something I'm missing?

The only way I can think of to do this fairly securely (allowing for the inherent limitations of running as NETWORKSERVICE) is to generate a one-time cookie with each prepared transaction, which cannot be discovered by listing the transactions. Then allow anyone in NETWORKSERVICE to make a database connection that can do nothing except commit/rollback prepared transactions, and only if it knows the transaction's cookie. That seems awfully fiddly though.

Does MS-SQL just assume that NETWORKSERVICE is trusted?


texts: Is there any index with O(1) complexity for lookup in PostgreSQL?

texts: Is there any index type in PostgreSQL that gives O(1) complexity for lookups? on strings?


texts: How do I access a user's application username when that application connects to the DB?

texts:
I'd like to audit changes to a table in SQLServer using a trigger and an audit table to do so. End-users connect to the database through an application. If I use SYSTEM_USER to obtain the end user's username, then it returns the name of the application that is connecting to the database, and not the user's unique login name for that application.

What would be a standard approach to capturing the user's unique application login name in this scenario?


texts: DELETE and INSERT - is this simple query statement sufficient to preserve atomicity?

texts:
I intend to update a table by going for a DELETE then INSERT on postgres in a single query. Obviously I want the DELETE to only take place if the INSERT is successful, i.e. preserve atomicity.

Is writing the query as below, sufficient for this?

DELETE FROM table_one WHERE some_id = 1;
INSERT INTO table_one(col1, col2, col3) VALUES (val1, val2, val3), (val4, val5, val6), ...

Thank you very much.

EDIT:

After reading around, I believe I am supposed to use transactions. Is this correct?

BEGIN;
DELETE FROM table_one WHERE some_id = 1;
INSERT INTO table_one(col1, col2, col3) VALUES (val1, val2, val3), (val4, val5, val6), ...
COMMIT;

So if DELETE fails, then the entire transaction fails?


texts: I am trying to make sense of the "memory optimized" setting for Azure Database for PostgreSQL.  Main memory data management can be viewed as quite different from secondary storage data management (like column stores, and other main memory optimized approaches to store data), hence my confusion. Would Microsoft simply create a ram-drive or simple allocate more RAM for data? We still need to flush all the data from RAM to disk in case of a possible loss of electric power or hardware failure every now and then, how all this is accomplished on such instances?


texts: INSERT/UPDATE stored proc deadlocking itself

texts:
I have a table (Database2.dbo.OrganizerDataDependencyChange) that contains information about when a row in certain other tables was last changed.  On each of these tables I have a trigger that calls a stored procedure (Database2.dbo.SaveOrganizerDataDependencyChange) that simply updates the Database2.dbo.OrganizerDataDependencyChange with the time that the trigger fired.  These triggers are fired from either Database1 or Database2, so they often are cross-database calls.

I'm getting read-write deadlocks on the Database1.dbo.OrganizerDataDependencyChange table when two different rows on the table are being updated and I don't understand why.  There's only one index on the table, and it's the clustered index that completely covers the query so no lookup deadlock is possible, and though there are two statements in the proc, I specifically re-wrote it to what I understand is the best way to avoid concurrency issues, with a WHERE NOT EXISTS instead of using IF/ELSE logic, so it shouldn't be coming at this index from different angles, right?  Since it's fired in triggers and cross-database I'm having some difficulty reproducing the issue, I can of course reproduce blocking but that should be fine and is what I expect.

Can someone help me understand what's going on here?  I  could probably fix it with a NOLOCK hint or maybe an applock but I still wouldn't understand why it was happening.

Here's the table:

CREATE TABLE [dbo].[OrganizerDataDependencyChange](
[TableID] [INT] NOT NULL,
[Database] [VARCHAR](150) NOT NULL,
[Updated] [DATETIME] NULL
)
CREATE CLUSTERED INDEX [CX_OrganizerDataDependencyChange_TableID_DB] ON [dbo].[OrganizerDataDependencyChange]
(
[TableID] ASC,
[Database] ASC
)

And here's the stored procedure:

CREATE PROCEDURE [dbo].[SaveOrganizerDataDependencyChange]
(
@TableID int,
@Database varchar(150)
)
AS
BEGIN

SET NOCOUNT ON;

    DECLARE @rowcount INT;

INSERT INTO dbo.OrganizerDataDependencyChange
        ( TableID, [Database], Updated )
SELECT TOP 1
    TableID = @TableID,
    [Database] = ISNULL(@Database, ''),
    Updated = GETDATE()
FROM dbo.OrganizerDataDependencyChange
WHERE NOT EXISTS
(
    SELECT
        1
    FROM dbo.OrganizerDataDependencyChange
    WHERE TableID = @TableID
    AND [Database] = @Database
)

SET @rowcount = @@ROWCOUNT  --How many rows were inserted?

UPDATE dbo.OrganizerDataDependencyChange
SET Updated = GETDATE()
WHERE @rowcount = 0 --Only run this if no rows were inserted
AND TableID = @TableID
AND [Database] = ISNULL(@Database, '')

END

And finally a couple of the deadlock graphs I'm getting out of XE that shows the ridiculous nested triggers:

Deadlock 1

Deadlock 2

MSSQL2014 Standard, if that makes a difference. Isolation level is the default Read Committed.  If there's anything else I can share to shed more light please let me know!


texts: what effect does changing the data type of an existing indexed field have on the index?

texts:
I have a few MySQL tables and I want to change the data type of some of the existing fields. The fields already have some single and multi-column indexes on them (all of which are type = "Normal").

I would like to know if changing the data types of any fields included in the existing indexes will have any effect on those indexes? Do I need to remove and re-create them for example or does MySQL handle that automatically?


texts:
I want to ask how do I get database size in previous day (example: Today is January 31st, and I want to get database size in January 22nd).
Note: I do not have regular backups.

I have tried this query:

SELECT TABLE_NAME, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = 'my_own_schema'

But I only have the size of current time.

Thank you for any helps!


texts: Multiple records and determine if a combination if values exist and write this to a new column


texts: Oracle Procedure alter pivoted data to unpivot and push into different destination

texts:
I have a PL/SQL procedure which takes string at runtime which is comma separated,splits it and pushes into table.
Example of procedure and table is given here:
Procedure is push_data.
Current table where data is getting inserted is push_data_temp.
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=fae31c11a4ad6205ebcaa418a0bffffd
Now as per requirement, i need to write a new procedure where logic would remain same of splitting data, but in the new table push_data_pivot(structure inside pivot), should display data pivoted in this format.
Ex: if currently data in push_data_temp is:

The data should go in push_data_pivot in the new procedure as:

The logic should remain same, just that the data getting inserted would go into columns instead of rows, populating id and value columns.
Here column id, is the unique identifier for the string passed in both tables. Basically, it is same as the one used in original table.
PUSH_DATA_PIVOT :
 create table PUSH_DATA_PIVOT
(
id_pk NUMBER,
  id NUMBER,
  label_id number,
  label varchar2(4000),
  value varchar2(4000)
);

insert into push_data_pivot values(1,null,'COL01',null);
insert into push_data_pivot values(1,null,'COL02',null);
insert into push_data_pivot values(1,null,'COL03',null);
insert into push_data_pivot values(1,null,'COL04',null);
insert into push_data_pivot values(1,null,'COL05',null);
insert into push_data_pivot values(1,null,'COL06',null);
insert into push_data_pivot values(1,null,'COL07',null);
insert into push_data_pivot values(1,null,'COL08',null);
insert into push_data_pivot values(1,null,'COL09',null);
insert into push_data_pivot values(1,null,'COL10',null);
insert into push_data_pivot values(1,null,'COL11',null);
insert into push_data_pivot values(1,null,'COL12',null);
insert into push_data_pivot values(1,null,'COL13',null);
insert into push_data_pivot values(1,null,'COL14',null);

The sample data is single string, for single label_id and corresponding set of column label values.However actual table would have multiple such strings inserted on basis of id column, same as original requirement.
The only requirement is, to create new procedure with new destination table as push_data_pivot and data in unpivoted format. Split logic would be same as in existing procedure.


texts: psql asks for password, CREATE USER didn't specify one

texts:
I used to have a user, say 'jack' in postgresql, and then I forgot what the password. I reassigned all its ownerships to user postgres, removed the user, and then created it again with CREATE USER jack;.

Now if I try to connect with it, psql asks for a password.

$ psql db_name jack
Password for user jack:
psql: fe_sendauth: no password supplied

What is going on and how do I check if a user has a password or not?


texts: How to know there is a maintenance job running on a postgresql database

texts: I have a python script on postgresql database which is fetching records from sql server database and inserting it to postgresql DB after some filtering. this jobs is running by cron every two minutes. I want to know when there is maintenance job such as autovacuum is running and if running forget fetching records. Is it possible to detect in python?


texts: How I can impersonate a login with securityadmin server role

texts:
On MSSQL 2008R2 I have two instances on different servers, and I need sync up the password changes for all logins.
I thing that i could create a store procedure where a user (impersonate) with securityadmin server role change the password.

Obs: CHANGE_PASSWORD is a user with securityadmin server role.

ALTER PROCEDURE SPU_CAMBIO_PASSWORD
(
    @USER VARCHAR(50),
    @PASSWORD VARCHAR(100)
)
WITH EXECUTE AS 'CHANGE_PASSWORD'
AS
    DECLARE @STATEMENT VARCHAR(200)
    SET @STATEMENT= 'ALTER LOGIN '+ @USER+ ' WITH PASSWORD = '''+@PASSWORD+''', CHECK_POLICY =OFF'
    EXEC(@STATEMENT)
GO

Error:
Cannot execute as the server principal because the principal "CAMBIO_PASSWORD" does not exist, this type of principal cannot be impersonated, or you do not have permission.


texts: How to hide/disable tables without dropping them to check redundancy?

texts:
I have to maintain and extend an old legacy system which contains webservice methods and database tables that are no longer used. Since I’m not entirely sure that the tables are really redundant, I’m afraid to drop them.

Is there any other way to achieve the same effect (tables cannot be used any more) without dropping them? My idea was to transfer them to a different schema (e.g., Deleted) from the current default, dbo.

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Deleted')
BEGIN
   EXEC('CREATE SCHEMA Deleted')
END

ALTER SCHEMA Deleted TRANSFER dbo.TableName;

Is there any other option or are there any drawbacks to the schema approach?


texts: What would cause a transactional replication subscription to restart repeatedly with no errors?

texts:
I have set up a transactional replication publication/subscription to test a migration from an on-premise SQL 2016 Enterprise Edition installation to Azure SQL DB and I'm finding that when the database snapshot finishes uploading to Azure, it just starts over again from the beginning. It finishes within 5-6 hours and uploads ~125GB of data, so I don't believe it is anything to do with the subscription expiring or exceeding any timeout thresholds. It is set to run continuously, rather than on a schedule.

I've looked at the distribution history in the MSdistribution_history table and can't see anything which mentions the subscription being marked for reinitialization, all that is there are entries showing 'Bulk copied data into table ...', and then at the point where I can tell it restarted, it goes back to 'Creating primary key index on table...' and 'Applied script x...'. I have also looked at the MSrepl_errors table and msdb..sysreplicationalerts and both are empty.

I have tried deleting the Azure SQL DB and publication and going through the process from the beginning again, but I am seeing the same thing after having done this.

Is this something that someone has seen before and could explain the cause of and how to fix it?


texts: Never changing the password for distributor_admin for local instance only?

texts:
Per Does distributor_admin need sysadmin? distributor_admin needs SA and per Can the account name of 'distributor_admin' be changed? the name can not be changed.

The password is changed with sp_changedistributor_password See sp_changedistributor_password (Transact-SQL) for details.

  When the distribution database is contained on a local instance, a random password is generated and configured automatically. Source

What this all means is that when you have replication on a local instance (i.e. reporting database) no one knows what the password is.  It would seem to be impossible to compromise the password. Assuming you are watching for and reporting on failed login attempts, you would know if anyone was trying to brute force the password.  This leads to an argument where there is no good reason to actually change the password on a regular bases.  You might want to change it on day one, if you don't trust the random password generation, but even that could add more risk then it removes, as now the password has been seen and processed with human contact.

Is there a good reason to change the password for distributor_admin for local instance only?


texts: How does a tagging article database look like?

texts:
I have a article website and each article has some tags. How should be my database structure?

This is my article table (article):

article_id (key)  |  article_content  |  article_title  |  article_lang_id

This is the list of my tags (tag_list):

tag_list_id (key)  |  tag_list_name  |  tag_list_description

And this is my tag table (tag_link):

  tag_id (key)   |   article_id (f-key)  |   tag_list_id (f-key)

I echo the article content with php, how can I echo (I mean showing tag bellow content) tags bellow the content?

This is my question:

1- How can I show each content tags near content?

(each content can has less than 5 tags)

2-What is the MYSQL query that I can run to select my tags with content?
(is there any professional MySQL orders to select content with tags form tag_link and then select the name of tag from tag_list and then show it near article, can I do it with MySQL orders like join, having, on and several other? How?)

3-Is my database structure for tagging true?

(I can do this with php variables and ... , but I need to do it by MySQL)


texts:
I have sql query as

select sourceIP, sum(sourceBytes)
from flows
group by sourceIP
order by sum(sourceBytes) desc

This brings result (dummy) as:-

sourceIp     SourceBytes
192.168.1.2  100
192.168.1.3  79
192.168.1.4  67
192.168.1.5  4
192.168.1.6  4

Now if I change the query to

select sourceIP, sum(sourceBytes)
from flows
where sourceBytes > 50
group by sourceIP
order by sum(sourceBytes) desc

The output be

 sourceIp        SourceBytes
 192.168.1.2     150
 192.168.1.3     40

I don't have access to DB right now, I cannot pull /show real table values, but the point here I want to make with the greater then statement the output is changed. I was of the view with second query I just want to process the results instead of all the values present flows table to just values which are greater range i.e 50. I want to know what level these two queries are not the same. Thanks.


texts: mongodb disable write access (while db migration)

texts:
I want to migrate from one cluster to another. So I'm goint go make a backup with mongodump and mongorestore. Db is not so big, so I would like to disable writes on source db before the start to exclude accidental writes from apps.

What would be the best way to do this?


texts: Tool for Reverse Engineering of PostgreSQL Database on Linux (Ubuntu)

texts:
Please suggest a tool to reverse engineer a PostgreSQL DB into an entity-relationship-diagram (ERD) or equivalent ??

Ideally, I'm looking for a simple ERD or UML class diagram in a format that is easy to work with (SVG, PDF or any common UML file format).

So far, I founded only software for MySQL and it's for Windows,
but that's not solution.


texts: How to add FK on a column of composite type in PostgreSQL?

texts:
I have a table which contains a composite type and I am trying to connect a column of it to another table's PK.

CREATE TABLE aaa (col1 SERIAL PRIMARY KEY);

CREATE TYPE bbb AS (col2 INTEGER);
CREATE TABLE ccc (col3 bbb);

I tried to add FK but it was syntax error.

CREATE TABLE ccc (col3 bbb, FOREIGN KEY col3.col2 REFERENCES aaa col1);

ERROR:  syntax error at or near "col3"
LINE 1: CREATE TABLE ccc (col3 bbb, FOREIGN KEY col3.col2 REFERENCES…

This is also a syntax error.

CREATE TABLE ccc (col3 bbb, FOREIGN KEY (col3.col2) REFERENCES aaa (col1));

ERROR:  syntax error at or near "."
LINE 1: CREATE TABLE ccc (col3 bbb, FOREIGN KEY (col3.col2) REFERENC…

Also this…

CREATE TABLE ccc (col3 bbb, FOREIGN KEY ((col3).col2) REFERENCES aaa (col1));

ERROR:  syntax error at or near "("
LINE 1: CREATE TABLE ccc (col3 bbb, FOREIGN KEY ((col3).col2) REFERE…

How can I add FK to a sub-column which is a column of a composite type?
I am not trying to make multi-column FK. I wan to link a single INTEGER column to another INTEGER column.

I am using PostgreSQL 9.3.

Update

This is simplified version of my current schema. I am essentially abstracting tables into hierarchical key-value set.

CREATE DOMAIN           "AlwaysSUID" AS BIGINT NOT NULL;
CREATE DOMAIN           "OptionalSUID" AS BIGINT DEFAULT NULL;

CREATE DOMAIN           "AlwaysString" AS TEXT NOT NULL;
CREATE DOMAIN           "OptionalString" AS TEXT DEFAULT NULL;

-- More similar domains...

CREATE TYPE             "Account" AS
(
    "socialName"            "AlwaysString",
    "mailAddress"           "AlwaysString",
    "passcodeHash"          "AlwaysString"
);

CREATE TYPE             "Session" AS
(
    "accountID"             "AlwaysSUID",  -- This needs to be linked to `AccountMap.ID` as FK.
    "chracterID"            "AlwaysSUID",
    "initiationTime"        "AlwaysTimestamp",
    "deviceNameID"          "AlwaysString"
);

CREATE TABLE            "AccountMap"
(
    "ID"                    BIGSERIAL PRIMARY KEY,
    "content"               "Account"
);

CREATE TABLE            "SessionMap"
(
    "ID"                    BIGSERIAL PRIMARY KEY,
    "content"               "Session"
);


texts: Detail of all transactions on a specific row

texts:
I've a User table and It's data can be added/ updated from a User Screen. I want detail of all transactions on any row.
For example, I have data in table like this

Name - Age -  Salary
John    -  25 - $10
Jazy    -  23 - $10

Then some user update john's Age like this

Name - Age -  Salary
John    -  20 - $10
Jazy    -  23 - $15

and then Update its Salary like this

Name - Age -  Salary
John    -  20 - $20
Jazy    -  23 - $15

I want a query to get all transactions on John's records like this

John    -  25 - $10
John    -  20 - $10
John    -  20 - $20

Is there any way to get data like this??


texts: How to design table for role-based security with special Update actions

texts:
I am trying to design a table that will contain the following information. Basically this is a table to lookup security roles. The catch for this is that for each ObjectType, you might have special actions they can or cannot do.

For example, ObjectType A, you can Assign, Complete, Close, for ObjectType B you can Recieve, Release, and Reject. For each of these special actions they can or cannot do them.

UserID
ObjectTypeID
Create (yes or no)
Read (yes or no)
Delete (yes or no)
Update (yes or no, in addition to a list of ActionID's that are
specific to ObjectType)

note: int? = nullable integer

I came up with the following table structure, but the problem is that I would have to add another column if I wanted to I realized I don't have enough columns to contain all possible actions for that specific ObjectType. Also, that just seems kinda of sloppy, and not very Normalized.

How I can make this more Normalized?

UserID|ObjectTypeID|Create|Read|Delete|Update|Action1|Action2|Action3|...
int   |   int      |  bit |bit | bit  | bit  | int?  |  int? |  int? |...

ObjectTypeID|ObjectType|Description
  int      |    int   |  varchar

ActionID|ObjectTypeID|ActionDescription
  int   |    int     |    varchar


texts: Oracle user locks (dbms_lock): guaranteed order of requests granted?

texts:
I was wondering if Oracle guarantees that the sequence of dbms_lock.request granted is the same as the sequence in which they were requested.
In my tests it always was the case, but I couldn't find any official statement in the Oracle documentation.

So let's assume we have at least three sessions:

Session 1 requests the lock via dbms_lock.request(1) and is granted the lock immediately (as the lock is not yet held by any other session)
Session 2 requests the lock via dbms_lock.request(1) and has to wait
Session 3 requests the lock via dbms_lock.request(1) and also has to wait
Now Session 1 releases the lock via dbms_lock.release(1) (or trx end or session end)

Is it guaranteed that Session 2 will now receive the lock and Session 3 still has to wait until Session 2 releases? Or might Session 3 receive the lock before Session 2?

Edit: if the answer is "yes, guaranteed", please provide link to Oracle documentation, whitepaper, V$ view or the like.

Edit 2: Database version in question is 11g and up


texts: RegisterAllProvidersIP 1 or 0 For The Cluster Resource

texts:
I have set up clustering across two sites, at each site there is a single instance of SQL 2014 enterprise and I am using Availability Groups.

I know I need to set RegisterAllProvidersIP for the AG Network name in order for the listener to check both IP Addresses.

One thing I'm not sure about is if I need to set RegisterAllProvidersIP for the actual cluster name??

Object Name Value Type

??????AG01_??????01 RegisterAllProvidersIP 1 UInt32

Object Name Value Type

Cluster Name RegisterAllProvidersIP 1 UInt32 <-----?????????????

Thanks in advance


texts: Will a change from MyISAM to InnoDB require SQL query changes in MySQL?

texts:
I am planning to change some large MyISAM tables in my MySQL database into InnoDB.

This is because I am having performance problems where large and complex updates (updating a few million rows in one go) are stopping users from concurrently accessing that tables.

I understand that if I convert the table to InnoDB then the writes/updates will cause row locking rather than table locking.

If I do this, Will have have to review all my SQL selects/insert statements, and my stored procedures, and make any changes due to the engine change, or will they all 'just work'?


texts: Does MySQL still handle indexes in this way?

texts:
Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index.

  If a table T is a MySQL table having four indexes (ndx1,ndx2,ndx3,ndx4)
  and you want to 'alter table T drop index ndx3;' here is exactly what
  happens under the hood:

  1) MySQL copies T.MYD to a temp table, i.e., S.MYD and a zero byte S.MYI.
  2) MySQL does 'alter table S add index ndx1 (...);
  3) MySQL does 'alter table S add index ndx2 (...);
  4) MySQL does 'alter table S add index ndx4 (...);
  5) MySQL deletes T.MYD and deletes T.MYI
  6) MySQL renames S.MYD to T.MYD, and renames S.MYI to T.MYI

Is this still true? Is his advice still valid?

  Given the same MyISAM table T having four indexes (ndx1,ndx2,ndx3,ndx4)
  and you want to 'alter table T drop index ndx3;' try this instead:

  1) create table T1 like T;
  This creates an empty table T1 with indexes ndx1,ndx2,ndx3 and ndx4.
  2) alter table T1 drop index ndx3;
  This drops index ndx3 on the empty T1, which should be instantaneous.
  3) insert into T1 select * from T;
  This will populate table T and load all three(3) indexes for T1 in one pass.
  4) drop table table T;
  5) alter table T1 rename to T;

How do you all handle adding and removing indexes from large tables?


texts: How do I transfer logins from 2008R2 to 2014 with passwords?

texts:
I've tried Tasks>Generate Scripts... and set 'Script Logins' in the Advanced Scripting Options, but I get this in the output:

/* For security reasons the login is created disabled and with a random password. */

Is there a simple way to script these logins with passwords or another way to transfer logins from a 2008R2 instance to a 2014 instance?


texts: Mysql table crash, what do I need to do to fix this

texts:
Table '.\mysql\proc' is marked as crashed and should be repaired.

Can anyone help me figure out how to fix this?


texts: Indexing a date column in MySQL doesn't work if a date function is used

texts:
I have a table with the following schema
table_name

id int
task_id int
completed_date datetime (INDEX IX_TBL_NAME_COMPLETED_DATE)

I run EXPLAIN on this query
EXPLAIN
    SELECT
        *
    FROM table_name TBL
    WHERE
        TBL.completed_date BETWEEN date1 AND date2

This query runs and fetches records using the index on completed_date
However,
While running the same query with a date function
EXPLAIN
    SELECT
        *
    FROM table_name TBL
    WHERE
        CONVERT_TZ(TBL.completed_date, timezone1, timezone2) BETWEEN date1 AND date2

The index isn't made use of resulting in a slow query.
Can someone explain the reason behind this behavior and solution to optimize queries like this?


texts: Conditional formatting based on number of days in SSRS against a cell value in reference to another field

texts:
I have a SSRS report where I have cell displaying a field called activity_date. I would like this to be colour coded based on a number of days against another dataset field called valid_to where if it is greater than 14 days it is green, within 14 days it is yellow and if it is later than the valid_to date it is red. Can any one help me?

I have made some changes to the requirements:

RED     valid_to is prior to today AND cell_value is null
ORANGE  valid_to is prior to today AND cell_value is NOT null
YELLOW  valid_to is between today and 14 days ahead of today and cell_value is null
BLUE    valid_to is in the future and cell_value is NOT NULL
GREEN   valid_to is 15 or more days ahead of today and cell_value is NOT NULL


texts:
Does the timestamp data type represent an instant-in-time or a year-month-date-hour-minute-second value? An instant in time is a moment, like the moment you started reading this, and it can be described with many different y-m-d-h-m-s+timezone values. Without timezone, it's ambiguous, the same moment can be 17:25 for me and 13:25 for someone else.

Here's what I think I get. The current_timestamp function returns the current time in the current timezone, so this is an unambiguous instant, the real now. Inside a table, it is stored as UTC, so with this assumed timezone, it should also be unambiguous.

Now here's what I don't get: it's the existence of the utc_timestamp function. I stored a current_timestamp and an utc_timestamp inside the same column, and they were different. This function shouldn't even exist, I don't understand why it represents a different instant in time.


texts: website performing slow once mysql connection RDS reached 50

texts:
Below the rds parameter group config, please help me to resolve this issue.
I am using 4 web server having 2 CPU and 8 GB ram and rds having 4 cpu and 16 GB ram.

Once the connection reached 50 site will stop responding.

join_buffer_size                        150.00 MB
read_buffer_size                        150.00 MB
read_rnd_buffer_size                    300.00 MB
sort_buffer_size                        150.00 MB
Total for max_connections=600           750.00 MB


texts:
I'd like to incorporate this command line in a postgres 8.4 version function:
echo "this is the body" | mail -s "subject" -r sender@sender.com "recepient@recepient.com"

Is it possible?
I looked at this googling it but I can't use sendmail, is there something similar I could use but instead of sendmail, just mail?:
open(MAIL, "|/usr/sbin/sendmail -t") or
die 'Cannot send mail';
print MAIL "From: $from\n";
print MAIL "To: $to\n";
print MAIL "Subject: $subject\n\n";
print MAIL "$content";


texts: Filter records and find those with minimal date

texts:
I have the following tables in Microsoft SQL Server:

Users: Includes uID (primary key), birthDate, name
Conversations: Includes convID (primary key), hostID (foreign key for uID in Users)
Participants: Includes uID (foreign key for uID in Users), convID (foreign key for convID in Conversations).

I need to write a query which finds the name, ID and birth date of the oldest user who didn't participate in any conversation, and that his name contains the letter 'b'. If there is more than one user, I need to return all of them.
I don't know how to both filter the users, and than find those with the minimal birth date (the oldest).


texts: Can an active Postgres serverlog be truncated?

texts:
Running Postgres 9.1 on Ubuntu 14.10 LTS.

I've got an application running on a machine that's going to run out of diskspace soon.  However, the application is in the middle of a crucial run that has taken 6 days thus far, and canceling it would mean another 6-day run.

I've deleted everything I can for which I have permissions, but I'd like to truncate the server log to free up some space.

If I were to copy the server log off and then do a cp /dev/null /database/serverlog would I risk causing the server to hang?  Or since the file reference has not changed, will it keep chugging along?

I realize the server log I copy off will have incomplete entries at the end and the new servlog will have incomplete entries at the beginning, so it's obviously not an optimal situation if I had to try to restore data using those files as there would be transactions missing.  However, I think the likelihood of actually needing to use the serverlog files before I have a chance to reconfigure and restart the server.


texts: How can I setup a Login which can run an automated Database Refresh without giving away too much priv?

texts:
I would like to re-ask and extend the question posed in this thread if I may:

SQL Server permissions to create, restore, delete only some databases

I am also attempting to setup an 'isolated' Login-A on a server I'll call Target (SQL Server 2012), with just enough priv to run a stored procedure (with a @dbname argument) which does the following:

runs a Backup for @dbname on a different linked server (Source)
Alters @dbname offline and Drops it on the Target server
then Restores new @dbname on Target

I played with "Grant Create Any Database To Login-A", which does appear to fence Login-A from neighboring databases. But when I tried to run Alter as part of some Create Database DDL, it failed, so this doesn't seem to help the stored procedure sequence.

How do we give enough priv to Login-A to be able to Alter and Drop without it being turned on beyond the current DB being created? I want my users to be able to refresh their databases by themselves (via the proc) while protecting all the other databases on the server which they don't own and shouldn't be able to touch (and without creating an audit violation).


texts:
I have a weird issue in the reports that I have created in SSRS 2008.

On the report I have got a number of tablixes defined, that should be displayed attached together vertically. Imagine they form a big table which each row (or set of rows) of this imaginary table has got different number of columns.

Using the alignment and width icons, I make these tablixes width the same. In design mode all these tablixes look nicely attached to each other with the same width, however when I run the report, the width of them appear unequal! I don't have any margin or padding set for them and can't understand why what I see in design mode is not consistent with what is displayed in run (review) mode!

Any idea?

Thank you.


texts: Will table partitioning speed up my queries?

texts:
My database is set up with what we call our staging tables and our live tables. Currently for we have about 2 million rows for a table called products. Our customers we call manufacturers load their data into the staging tables and when they are done updating, adding, deleting data we then push that data into our live tables.

These live tables feed a web service for a mobile app and websites. The pushing of data from staging to live consists of deleting all the data in the live tables and inserting all of the data from the staging tables. All of this is segregated by a column called ManufacturerID that exists in every table.

Some manufacturers have 500 products, some have 75,000. Depending on this we sometimes have REALLY REALLY slow web service responses because of paging through all of the 2 million records. Deleting the data from the live tables also seems to be getting ridiculously slow.

Would partitioning my products table by ManufacturerID help this situation? From what I have read this would basically mean when I am querying my products I would only be querying a small subset of the database on that ManufacturerID and therefore see a huge improvement in overall response time.


texts: Schema for large amount of machine's sensor data variables

texts:
I plan to do a database for more machines. Each machine will have multiple sensors mounted that store data like temperature, current, power, fault, manual mode and so on every minute. Each machine consists of multiple parts and the same data sets are stored for each part.

Every part may be divided into two subparts and each part/subpart can have multiple sensors for temperature. So in reality system can cosists of six parts, only part five has two subparts. Each part or subpart has one sensor for a variable stored except temperature. So for example part1 will have one variable for power, current, voltage, manual mode but can have multiple sensors for temperature.

There can be a system with more than 500 variables recorded every minute, and each variable type has a different type of value, e.g., some are integers, some are floats with one decimal some with two decimals. I can't setup one column for this. That's why I want to make the best structure possible but I have never really performed database design since I have always used only simple tables with small data.

I just don't want to make a table with 500 or more columns since I suppose that is not a good idea.

I would do something like this:

sensor_name, type, value, timestamp

I know according to the sensor name from what part/subpart is the reading.

However, with a large machines this table will have like 500 rows for every minute.

Thank you for every idea sugested.


texts: How to model common data for different kind of objects in a relational database?

texts:
In a current project, we are creating an application with some entites like Customer, Project, Company, Ticket, Request and more. The model of these is straight forward. The complicated part where I can't get my head around is the following:

We would like to have some common entities that can be "attached" to various objects. Some examples include Documents, Tags and Notes. Each of those should be manageable for each of the above mentioned domain entities.

In the server-side code (Java), we want to implement the common functionality as components and reuse/embed them on the specific edit pages of our domain objects. We are using an ORM and would also like to reuse those "metadata" classes.

I think this use-case would be easy to model with a document database.
But how would this be done in a relational model?


texts: Is it possible to setup multiple RW Replicas for a database in an AlwaysOn Availability Group?

texts: Can I have multiple RW replicas of an AlwaysOn Availability Group database across different servers? (I.e. two-way replication so that multiple servers can write to the same DB.)


texts: Getting the primary key columns off a table

texts:
I have a postgresql 11 database and I am trying to follow this official post on getting the primary key column from a table. However I get an error saying that my table is not an existing column.

ERROR:  column "tablename" does not exist
LINE 10: ...* from pg_index WHERE indisprimary AND indrelid = "tablename"...
                                                              ^

The code looks like this:

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = "tablename"::regclass -- <--- This is the problem
AND    i.indisprimary;

I believe the issue has something t do with the ::regclass cast as looking at the full table and replacing "tablename"::regclass with the indrelid number works fine, in my case it was 16490.

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 16490 -- "tablename"::regclass <--- replace with the number
AND    i.indisprimary;

Any ideas on why the official example is not working and how I can fix it?


texts: Is it a good idea to split MySQL DB into two servers

texts:
I have a MySQL DB server that accepts a lot of mobile application data for analysis. I would like to know if for optimization reasons the following architecture makes sense and is appropriate:

I would like to split the MySQL DB server into two separate servers as follows:

Server A (Master) would receive the statistic data for computation.
Server B (Master) would keep

Aggregated (pre-calculated data) the Master on-demand
application
user data.

The relation b/w servers would be Master-Master.

Server A -> Server B : sending aggregated (calculated) data
Server B -> Server A : sending new application and user data so that aggregation will also be performed for newly tracked applications.

If my reasoning is correct, can you shed some light on how I might accurately do this?


texts: Conditional one to many queries in mysql

texts:
I have a simple patients table and lots of Lab tests table. Every patient can have many Lab tests. What i want to do is to only  query the tests which are already entered for that specific patient and avoid joining all 100000 tables(which will take lots of resources to execute).

Should i create a table and save which tests are conducted for the patient for later use?

PS: i have to mention that currently im using MYSQL and Doctrine 2(which is an ORM)and I'm out of options for using non-mysql solutions and i know that there are many Electronic medical records softwares using SQL but they are not opensource so we cant study them.


texts:
I need to properly format some European addresses.
One of the steps is to capitalize the first letter, but avoiding some specific words, like 'on', 'upon', 'von', 'van', 'di', 'in', 'sul'.
Therefore, although my skills are scarce, I thought it was a good idea to use a RegEx-based function.

After some googling I found this here:

CREATE FUNCTION InitialCap
(
    @String nvarchar(max)
)
    RETURNS nvarchar(max)
AS
    BEGIN
        DECLARE @Position INT;

        SELECT
            @String   = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
            @Position = PATINDEX('%[^A-Za-z][a-z]%',@String COLLATE Latin1_General_Bin);

        WHILE @Position > 0
            SELECT
                @String   = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
                @Position = PATINDEX('%[^A-Za-z][a-z]%',@String COLLATE Latin1_General_Bin);

        RETURN @String;
    END

That seems to search for a sequence of a 'non-letter' + a lowercase 'letter'

[^A-Za-z][a-z]

OK, I think I've understood how it works, and I modify it to best match my needs.

I supposed it was better to search for a space or ' or - and a lowercase letter, therefore I changed it to

[\s'-][\w]

Then, after several attempt, I built this RegEx on regexr.com, that seems to catch the required sequence:

[\s](?!di\s|in\s|sul\s|on\s|upon\s|von\s|uber\s|ueber\s)[\w]

But when I put that into the above function, the result is not as expected.

What's wrong?


texts: Which problems arise, declaring the size of all varchar parameters as max in stored proc?

texts:
Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time.

Increasing just the size of a column in a table is a rather simple task.

But when your shop adheres to the rule that the length of stored procedures parameters has to match the length of the corresponding table columns, an avalanche of changes through your stored procedures goes down hill.

Locking at Oracle shows, that procedures don't need to know about the length of varchar2 parameters.

What are the pros and cons, when you just declare each varchar parameter as varchar(max) or nvarchar(max)?

Obviously you can forget the maintenance nightmare of having to adjust your procedures each time you increase your column sizes.

Answer to SQLRockStar:
I want to keep a rather conservative table design, where fields, with a known limited length are modeled according to the current restrictions without reserve for possible future extensions.

Many of my tables contain an id int, a code varchar(30) and a description column varchar(255).

The code field has to reflect local naming conventions of different customers.

Some costumers need this field increased to 50.

When I do not code the length into the procedure parameters, I need only adjust the table column of this single costumer.

When I insist to code the length into the parameters, then I have either to support different versions of procedures for different customers, or I have to change and roll out the change  to all customers.

Example

-- SQL Server
Create Table dbo.SQLServerTable (
    id int identity(1,1) primary key not null,
    code varchar(30) not null,
    description varchar(255) not null
);
go
Create Procedure WrapInsertSQLServer1 (
    @code varchar(30),
    @description varchar(255)
) as
INSERT INTO SQLServerTable values (@code, @description);
go

Create Procedure WrapInsertSQLServer2 (
    @code varchar(100),
    @description varchar(1000)
) as
INSERT INTO SQLServerTable values (@code, @description);
go

Note: When you increase the length in SQLServerTable by some character, you have to adjust WrapInsertSQLServer1, but WrapInsertSQLServer2 doesn't need to be changed.

-- Oracle
Create Table OracleTable (
    id NUMBER(10) primary key not null,
    code varchar2(30) not null,
    description varchar2(255) not null
)
/
CREATE SEQUENCE S_OT START WITH 1
/

Create OR Replace Procedure WrapInsertOracle (
    p_code varchar2,
    p_description varchar2
) as
begin
INSERT INTO OracleTable values (S_OT.NEXTVAL, p_code, p_description);
end;
/

Here the parameters of a stored procedure never have a length.

If you say WrapInsertSQLServer2 isn't OK => you are saying Oracle is inherently wrong


texts: Requesting advice on creating a table controlled SQL job scheduler

texts:
I have been tasked with cleaning up a series of SQL jobs that send email updates about various data and system conditions in my company. Currently, there are a ton of jobs, each with its own SQL code and each sending out its own individual email. The jobs are all on a schedule, some execute every 2 hours, some every 15 minutes, some once a week.

What I want to accomplish:

Turn each job into a stored procedure.
Create a user control table so that users only receive relevant alerts via SQL mail.
Have one SQL job that controls everything from a job control table.

Basically, I want to move the maintenance and scheduling of these alerts out of SQL Server Agent and put it into a control table. Can this be accomplished so that each stored procedure runs on its own schedule without the need for having numerous, unwieldy jobs?


texts:
I have the MySQL slow log feature enabled: http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

But sometimes the query_times are high simply due to high CPU load.

How can I append the current CPU load to each entry in the MySQL slow log (it writes to a file)?


texts: Mongos unable to find document after migration

texts:
I have set up a mongo cluster as follows:

Four shards => mongoshard1 (Disk1), mongoshard2 (Disk2), mongoshard3(Disk3), mongoshard4(Disk4)
Replica set of one configuration server => mongoshardconfig (Disk5)
One mongos server => mongos
Shard key is a hashed index of _id

The entire setup is on a single server and the various mongod instances have been setup on different ports. (Setting up on a single server instead of different nodes has been a conscious decision though against recommended practices)

My server got terminated and I created a new cluster on another server with exact same settings as above using the old disks. The sharded collection document has a field: url . For a particular id, if I search using _id, mongos returns null but returns the object on finding using the url. Connecting individually to shard2 returns the object on querying using same _id. Some of the documents are found by querying on _id through mongos and some are not.

Why is mongos unable to find the document using id? Can this be fixed?


texts: DPKG error: uninstall percona xtradb server 5.7

texts:
Trying to uninstall percona, as I broke installation. No luck:

>$ sudo apt remove percona-xtradb-cluster-server-5.7
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages will be REMOVED:
  percona-xtradb-cluster-server-5.7
0 upgraded, 0 newly installed, 1 to remove and 23 not upgraded.
1 not fully installed or removed.
After this operation, 104 MB disk space will be freed.
Do you want to continue? [Y/n] y
(Reading database ... 179533 files and directories currently installed.)
Removing percona-xtradb-cluster-server-5.7 (5.7.20-29.24-0ubuntu2) ...
dpkg: error processing package percona-xtradb-cluster-server-5.7 (--remove):
 installed percona-xtradb-cluster-server-5.7 package post-removal script subprocess returned error exit status 1
Errors were encountered while processing:
 percona-xtradb-cluster-server-5.7
E: Sub-process /usr/bin/dpkg returned an error code (1)

apt remove/purge/--force, nothing helps.

Removal with dpkg produces same:

sudo dpkg --remove  percona-xtradb-cluster-server-5.7
(Reading database ... 179533 files and directories currently installed.)
Removing percona-xtradb-cluster-server-5.7 (5.7.20-29.24-0ubuntu2) ...
dpkg: error processing package percona-xtradb-cluster-server-5.7 (--remove):
 installed percona-xtradb-cluster-server-5.7 package post-removal script subprocess returned error exit status 1
Errors were encountered while processing:
 percona-xtradb-cluster-server-5.7

What is weird... I can start, stop, restart process with no issues:

sudo systemctl status mysql
● mysql.service - LSB: Start and stop the mysql (Percona XtraDB Cluster) daemon
   Loaded: loaded (/etc/init.d/mysql; generated)
   Active: active (exited) since Tue 2018-06-26 00:21:05 CEST; 31min ago
     Docs: man:systemd-sysv-generator(8)
  Process: 5500 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)

lip 26 00:21:05 tkojemile systemd[1]: Starting LSB: Start and stop the mysql (Percona XtraDB Cluster) daemon...
lip 26 00:21:05 tkojemile systemd[1]: Started LSB: Start and stop the mysql (Percona XtraDB Cluster) daemon.

Also dpkg-query shows I assume - rH (removed|Half-installed):

dpkg-query -l percona-xtradb-cluster-server-5.7
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name                                          Version                     Architecture                Description
+++-=============================================-===========================-===========================-===============================================================================================
rH  percona-xtradb-cluster-server-5.7             5.7.20-29.24-0ubuntu2       amd64                       Percona XtraDB Cluster database server binaries

Anyone have idea how to uninstall/reinstall? I'm lack of options today...
Thanks in advance!


texts: Will PostgreSQL optimize query when the same aggregate usage?

texts:
SQL would not let use aliases (s,d) in other columns, so, my query is:

SELECT
   employe,
   sum(salary + bonus) AS s,
   sum(debt) AS d,
   (sum(salary + bonus) - sum(debt)) AS total
      FROM test_table
      GROUP BY employe;

This query contain aggregate sum(salary + bonus) two times.

Will PostgreSQL optimize it (use result from first aggregate in second aggregate) or do sum again for the second aggregate?


texts: Database full backup,differential backup and log backup plan

texts:
I am working with DB backup, first I would like to get a roughly plan on weekly/daily backup. I have countered some problems when I am trying to get such a plan, What I tried is:

For weeklyFULL DATABASE BACKUP, using: BACKUP DATABASE name To Disk... WITH INIT,SKIP;

Can I set it to SKIP and overwrite the old full backup file every week? If I did not add EXPIREDATE or RETAINDAYS, does that mean the current backup will not overwrite the existing one because the old one never expires?

For daily Differential DATABASE BACKUP, using BACKUP DATABASE name To Disk... WITH DIFFERENTIAL,INIT,SKIP;
How does the SQL Server know which full backup is daily differential based on? By using WITH FORMAT, MediaName='...'? If so, does that mean for the first time I do the full database backup I need to format a new media set such as DatabaseA set for that database, and for the later back up, I could use WITH NOFORMAT, medianame = DatabaseA' to let sql server knows that differential backup should be attached to that full backup database?

For transaction log backup, using BACKUP LOG A TO DISK...
I know it is not good to add WITH INIT to log backup, but how to handle the increasing size of log backup? Shrinkfile(log)each time I did the full database backup? Does that mean I could ignore the increasing size of transaction log before the next full backup comes?


texts:
How to compare values between two different tables?

I have two tables as shown below:

Table 'A'
sql_id       OPERATION       OPTIONS
63kf3an2j0pk SELECT STATEMENT
63kf3an2j0pkc TABLE ACCESS FULL

Table 'B'
sql_id       OPERATION     OPTIONS
63kf3an2j0pk SELECT STATEMENT
63kf3an2j0pkc INDEX UNIQUE SCAN IDX_THU

I need to compare the values in Table 'A' and Table 'B' for the given sql_id. My question is how to find the changed values.

In this case in Table 'A' the second line is TABLE ACCESS FULL. Table 'B' the the second line is INDEX UNIQUE SCAN IDX_THU. So here the value has changed. How can I find that using query? Something like when matched then "matched" when not changed "value changed".

I'm looking for output something like below:

 sql_id           comaparion outcome
dmqb1hzrf5uxh   No change in the plan
74anujtt8zw4h   plan changed

Can I do something like this?

select sql_id,case when ( select sql_id,OPERATION,options  from  a where sql_id='dmqb1hzrf5uxh'
minus
 select sql_id,OPERATION,options from  b where sql_id='dmqb1hzrf5uxh') is null then 'matched'  else 'changed' end as status;

I am comparing the explain plans before and after patch. The table 'A' is before patch result of the sqlid and table 'B' is after patch result. Now I want to compare the plans in both the tables. I want to compare the plan by using a query.


texts: General Question on how to setup db's for a social networking site

texts:
I am currently responsible for designing the entire database setup for a start up website.  It is quite a responsibility and I want to get it right, not only for the site, but for my own experience.  And I know this is really a general question, and obviously I can't reveal table structures I plan to have because of proprietary info etc, but I was hoping that there was someone on here who had some experience with designing a usable website with heavy reliance on a db for functionality purposes.
Basically, I want to know all the pitfalls that could occur and the general best practices.  Things like, how normalized should the table structure be?  Is it really the best idea to store user pictures directly into a table, or to just store a link to its directory on the site?  I know this is a very general question, even if you could point me to general tutorials on such things I would appreciate it.  Thank you for your understanding.


texts: Login Error while connecting SQL Database through Power BI?

texts:
I get the error message displayed in Power BI Online when using an enterprise gateway connected to an on-premise SQL Server

Unable to connect: We encountered an error while trying to connect.

  Details: Microsoft SQL: A connection was successfully established with
  the server, but then an error occurred during the login process.
  (provider: ssl provider, error: 0 - The signature of the certificate
  cannot be verified)

I have the Login credentials (SysAdmin) on server.

What is the cause of getting Login error? Any suggestions and resolution steps?


texts: Select from sys.server_file_audits returns wrong filename

texts:
I activated an audit on a SQL Server 2016 istance.
Version i SP1 CU3, Enterprise Edition.
With this query:

SELECT * FROM sys.server_file_audits

I get as log_file_path: O:\auditfiles\
In log_file_name: logins_3D6DA4C0-B388-4175-88A5-D2820733CFB7.sqlaudit

But when I query:

SELECT * FROM sys.fn_get_audit_file ('O:\auditfiles\logins_3D6DA4C0-B388-4175-88A5-D2820733CFB7.sqlaudit',default,default);

It returns nothing.
Checking on file system the real name of the file is:

  logins_3D6DA4C0-B388-4175-88A5-D2820733CFB7_0_131451051332700000.sqlaudit

Where the filename part _0_131451051332700000 is not retrived from query on sys schema.

When I select with correct filename i get the results.
I need to automate this in order to collect data from external queries.

How can I retrive the correct file name?


texts:
I have a table named Group in my database , when I try to work on it it gave error :

 SELECT GroupID,GroupName FROM Group

it said :

     Msg 102, Level 15, State 1, Line 1
     Incorrect syntax near 'Group'.

I know that Group is illegal name for table but its a old database with too many data and relations , and I cant rename this table , is there any way to work with table ?


texts: Is there any reason to have nonclustered indexes on a table with only 1 page in the clustered index?

texts:
I have a small but actively queried table in SQL Server that has 94 rows that are frequently read and\or updated. The clustered index safely fits into 1 8KB page and has significant empty space on that page. This leads me to believe that any future updates won't push it onto a second page.
In addition to the clustered index, over the years several nonclustered indexes have been added.
My question is, on a conceptual level, can the nonclustered indexes improve SELECT performance? If SQL Server can't read less than 1 data page at a time, then any nonclustered index read could be just as easily satisfied by reading the single page for the clustered index or am I misunderstanding the concept?
I don't wan to include the table definition, but here's some stats from sp_BlitzIndex on that table showing it's use:
ClusteredIndex      Reads: 1 (1 scan) Writes:180,544,146
Nonclustered Index  Reads: 0 Writes:103
Nonclustered Index  Reads: 63,425,182 (57,447,576 seek 5,977,606 scan) Writes:180,544,146
Nonclustered Index  Reads: 150,953,542 (150,953,542 seek) Writes:180,233,055
Nonclustered Index  Reads: 0 Writes:311,091

The clustered index doesn't seem to get read, but it I would think it could without the other indexes. Further interesting detail, sp_BlitzLock shows 39 deadlocks in this database and all 39 involved this tiny table, which I find interesting.


texts:
My machine disk is SSD and memory is 32GB.
My executed SQL:

SELECT `country_detail`.`country_name` AS `country_name`
FROM `statistic_detail`
  INNER JOIN `country_detail` ON (`statistic_detail`.`country` = `country_detail`.`country_code`)
GROUP BY 1;

The SQL need cost 30 second. So slowly! My explain is:

I don't understand why it need so much time?
I know how optimize it, I want to know why it is so slow.

My table information is:

CREATE TABLE `statistic_detail` (
  `date` varchar(10) NOT NULL,
  `os` varchar(10) NOT NULL,
  `ver` varchar(16) NOT NULL,
  `country` varchar(16) NOT NULL,
  `utype` varchar(8) NOT NULL,
  `stype` varchar(32) NOT NULL,
  `language` varchar(16) NOT NULL,
  `num0` bigint DEFAULT NULL,
  `num1` double DEFAULT NULL,
  `num2` double DEFAULT NULL,
  `num3` double DEFAULT NULL,
  `num4` double DEFAULT NULL,
  `num5` double DEFAULT NULL,
  `num6` double DEFAULT NULL,
  `num7` double DEFAULT NULL,
  `num8` double DEFAULT NULL,
  `num9` double DEFAULT NULL,
  `num10` double DEFAULT NULL,
  `num11` double DEFAULT NULL,
  `num12` double DEFAULT NULL,
  `num13` double NOT NULL,
  PRIMARY KEY (`country`, `ver`, `language`, `utype`, `stype`, `os`, `date`),
  KEY `ver_idx` (`ver`),
  KEY `language_idx` (`language`),
  KEY `utype_idx` (`utype`),
  KEY `stype_idx` (`stype`),
  KEY `os_idx` (`os`),
  KEY `date_idx` (`date`),
  KEY `country_idx` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

13731253 rows.

CREATE TABLE `country_detail` (
  `country_code` varchar(20) NOT NULL,
  `country_name` varchar(16),
  `region_code` varchar(16),
  `region_name` varchar(16),
  PRIMARY KEY (`country_code`),
  KEY `region_code_idx` (`region_code`),
  KEY `region_name_idx` (`region_name`),
  KEY `country_name_idx` (`country_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

334 rows.


texts: Modeling a Fact Table for Data that Changes Grain at one Point in Time

texts:
I am in the process of building a data warehouse data model for a data set, lets call it SALES, which spans in time from 2006 to present.
The problem:
The grain is consistent throughout except for one point, near the middle, where an additional dimension was added in.
Example:
Here's a simplified example that illustrates what I mean:
THE_DATE    CATEG   REGION  SALES
--------    -----   ------  -----
2006-01-01  shoes   NULL    233.00
2006-01-01  Hats    NULL     46.00
2006-01-01  Shirts  NULL    857.00
...
2013-01-01  shoes   Eastern 823.00
2013-01-01  shoes   Western 123.00
2013-01-01  Hats    Eastern 16.00
2013-01-01  Hats    Western 27.00
2013-01-01  Shirts  Eastern 457.00
2013-01-01  Shirts  Western 356.00

Some additional notes:

We're interested in the new dimension (e.g. we don't want to simply ignore that dimension and aggregate measures)
There's no way we can interpolate / allocate earlier values across the new dimension.
Need to be able to report on sales over the full time period.
Data is from a third party, and nothing can be done to change the grain at the source.

Possible Option:
One option is that I could include the REGION dimension key in the FACT table, and leave the REGION_KEY = -1 (which maps to an "Undefined" row in REGION_DIM, but that comes with it's obvious disadvantages e.g. new users might not realize the issue, etc. But this might be the option I have to go with.
Questions:

Are there other disadvantages to the option above? (single fact table, include the REGION dimension)
Are there any other ways in which this can be handled when modeling the target FACT table that might have fewer drawbacks?


texts:
I'm trying to get lines that have same ADAPTERID and Attribute_Name, but Attribute_Value can be active or designed automatically.
I used:

SELECT * FROM database1
where Attribute_Value IN ('ACTIVE','DESIGNED AUTOMATICALLY')
and Attribute_Name = 'State'
and ADAPTERID = '1277667'

and I get:

ADAPTERID   CFS_Name        Attribute_Name  Attribute_Value
1277667     1277667-Željka  State           DESIGNED AUTOMATICALLY
1277667     1277667-Željka  State           DESIGNED AUTOMATICALLY
1277667     1277667-Željka  State           DESIGNED AUTOMATICALLY
1277667     1277667-Željka  State           ACTIVE

How can I get these  that are actually only different ones:

ADAPTERID   CFS_Name        Attribute_Name  Attribute_Value
1277667     1277667-Željka  State           DESIGNED AUTOMATICALLY
1277667     1277667-Željka  State           ACTIVE

thanks


texts:
I'm creating a new publication for replication using the @allow_initialize_from_backup option to avoid the snapshot immediate creation and therefore locking all objects replicated on articles. The issue is that, either using SSMS wizard or tsql, when I start the publication (no subscription created yet), immediately I get lot of deadlocks on the tables been replicated. I'm checking all this information and more but I can't manage to do what I need. Haven't found a clear answer online. What I'm missing here?

sp_addpublication at MSDN
How to: Initialize a Transactional Subscription from a Backup (Replication Transact-SQL Programming)
Starway to SQL Server Replication
Replication Without Creating a Snapshot
similar question but different errorTransaction without snapshot?

And this is the code I'm trying to run:

EXEC sp_replicationdboption @dbname = N'db_name',
     @optname = N'publish',
     @value = N'true'
GO

EXEC sys.sp_addlogreader_agent @job_login = NULL,
     @job_password = NULL,
     @publisher_security_mode = 1
GO

EXEC sys.sp_addqreader_agent @job_login = NULL,
     @job_password = NULL,
     @frompublisher = 1
GO

-- Adding the transactional publication
EXEC sp_addpublication @publication = N'db_name',
     @description =
     N'Transactional publication of database ''db_name'' from Publisher ''distributor''.',
     @sync_method = N'native',
     @retention = 0,
     @allow_push = N'true',
     @allow_pull = N'false',
     @allow_anonymous = N'true',
     @enabled_for_internet = N'false',
     @snapshot_in_defaultfolder = N'true',
     @compress_snapshot = N'false',
     @ftp_port = 21,
     @allow_subscription_copy = N'false',
     @add_to_active_directory = N'false',
     @repl_freq = N'continuous',
     @status = N'active',
     @independent_agent = N'true',
     @immediate_sync = N'true',
     @allow_sync_tran = N'false',
     @allow_queued_tran = N'false',
     @allow_dts = N'false',
     @replicate_ddl = 1,
     @allow_initialize_from_backup = N'true',
     @enabled_for_p2p = N'false',
     @enabled_for_het_sub = N'false'
GO

EXEC sp_addpublication_snapshot @publication = N'db_name',
     @frequency_type = 1,
     @frequency_interval = 0,
     @frequency_relative_interval = 0,
     @frequency_recurrence_factor = 0,
     @frequency_subday = 0,
     @frequency_subday_interval = 0,
     @active_start_time_of_day = 0,
     @active_end_time_of_day = 235959,
     @active_start_date = 0,
     @active_end_date = 0,
     @job_login = NULL,
     @job_password = NULL,
     @publisher_security_mode = 1

EXEC sys.sp_addsubscription
    @publication = N'db_name',
    @subscriber = N'subscriber server',
    @destination_db = N'db_name',
    @subscription_type = N'Push',
    @sync_type = N'initialize WITH backup',
    @backupdevicetype = N'Disk',
    @backupdevicename = N'F:\Backup\db_name\',
    @update_mode = N'read only',
    @subscriber_type = 0;

-- Add subscription agent
EXEC sys.sp_addpushsubscription_agent
    @publication = N'db_name',
    @subscriber = N'subscriber server',
    @subscriber_db = N'db_name',
    @job_login = NULL,
    @job_password = NULL,
    @subscriber_security_mode = 1,
    @frequency_type = 64,
    @frequency_interval = 1,
    @frequency_relative_interval = 1,
    @frequency_recurrence_factor = 0,
    @frequency_subday = 4,
    @frequency_subday_interval = 5,
    @active_start_time_of_day = 0,
    @active_end_time_of_day = 235959,
    @active_start_date = 0,
    @active_end_date = 0,
    @dts_package_location = N'Distributor'
GO


texts:
How do I get a list of all the partitioned tables in my database?

Which system tables/DMVs should I be looking at?


texts: Cannot register SPN, error 0x80090350, state 4

texts:
This is my first time trying to configure kerberos. I need it for SQL Server.

Considerations

I'm running SQL Server 2008 R2 on Windows 2008 R2 server.
I have an Active Directory account for SQL Server 2008 R2.
The account is delegated for kerberos.
The account is configured to start SQL Server services. This works without problems.
SPNs are registered for both netbios and FQDN for computername and also computername:1433.
The same SPNS are registered for the server and the account.

I mean, if I do

setspn -L domain\serviceaccount

or

setsqpn -Q MSSSQLSvc/Server.domain.local,

I get

MSSQLSvc/Server:1433

MSSQLSvc/Server.domain.local

MSSQLSvc/Server.domain.local:1433

When I run this query:

select auth_scheme
  from sys.dm_exec_connections
 where session_id=@@spid

...the result is NTLM.

This query I'm running from another server, connecting to the SQL Server that I need to use Kerberos on.

When I restart SQL Server services I see this error in the log:

  The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x80090350, state: 4. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

Any idea what I'm missing?


texts: Do I need an auto-generated ID in addition to a unique field?

texts:
I have a table that represents a list of currencies. Here is a simplified version of it:

CREATE TABLE currencies (
  id          UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  code        varchar(3) NOT NULL UNIQUE,
  name        varchar(255) NOT NULL,
  precision   integer NOT NULL
);

As you can see, I have an auto-generated id field of type UUID, but then I also have a UNIQUE code field. Are there any benefits of keeping both id and code fields? I have an idea to drop code and use id as holder for code values, since I need uniqueness for all the currency codes anyways. So the new table would like this

CREATE TABLE currencies (
  id          varchar(3) NOT NULL PRIMARY KEY,
  name        varchar(255) NOT NULL,
  precision   integer NOT NULL
);


texts: How could I get the value as Y or N in one table if value is exists on another that meets certain condition?

texts:
I have to generate simple report from three tables named customer, product, and order. My report should have a column that shows whether the customer ordered that product or not. How could I get the Y or N value based on customer id  that exists on order with the product id? Sample table with data is as below:

customer
customer_id name    address phone
123 govinda nepal   16910833
234 arjun   nepal   15546235

product
product_id  name    brand   type    price
1   samsung gayalexy s5 samsung smartphone  55000
2   samsung gayalexy s6 samsung smartphone  65000

order
date    product_id  customer_id
1/1/2016    1   123
5/1/2016    2   123

And want the report column as below

cust_id    name      product_id    order_stat

and order_stat should be as Y or N.


texts: Increased rate of bad plans (Oracle ACS)

texts:
In the last weeks I noticed an increased number of cases where Oracle starts to pick notoriously bad plans for some statements on widely different systems (Exadata, ODA and AIX).

I haven’t seen that much before with our applications, so I wonder if this is related to a recent patch (the systems seems to have different releases/Versions). Or maybe the default analyser settings have changed?

In one particular case a primary (unique) index is not used for a simple primary key lookup but instead it seems to use some kind of index fast scan. I thought adaptive coursor sharing is actually not used for that kind of simple query.


texts: In SQL Server, are there any other options of data Import and Export

texts:
I've been working with SQL Server since SQL Server 7. So I know about BCP, Bulk insert, openrowset and SSIS.

My question is a simple one:

Do you know of any other ways to Import/Export data using SQL server
Express versions SQL 2005 to 2014 (and soon 2016).

I want to make sure that there isn't anything else due to the fact that the use of BCP (which is what I'm using right now) requires the use of xp_cmdshell and for security reasons, requires an xp_cmdshell_proxy account. I'm being challenged at work, and want to make sure nothing else exists, or that is new in Microsoft SQL Server (Express) before I respond.


texts: Recover oracle 10g database from old data files

texts:
I have an Oracle 10g database.

Unfortunately, our machine and system crashed and all the backups and Oracle archive logs, but we have old image copies of our data files and one of the control file (copy & paste)

How can I restore our database from these copies?

I tried to create a new database and replace the old files with new files, but I couldn't (I got message, we need to open the database with resetlogs, but we can't open it if we didn't make incomplete recovery)

Please help me


texts: Selecting one variant out of different similar results

texts:
I have a table that looks as follows:

Book    Format    Contents
A       html      ...
A       markdown  ...
B       html      ...
C       markdown  ...

I want to return only 1 version of a book. If a markdown version exists, I want markdown, otherwise I want html.

I'd love to see a SELECT statement that does this, and also a reason why it works.

I think this query is very close to what I need, but I don't understand how it works.

Thanks!


texts: Standards for storing a rating as a range (1-5, 1 is best, etc.)

texts:
I am designing a ROAD_INSPECTION table that has a CONDITION field:

+---------+-----------+
| ROAD_ID | CONDITION |
+---------+-----------+
|       1 |         2 |
|       2 |         5 |
|       3 |         1 |
|       4 |         1 |
|       5 |         4 |
+---------+-----------+

Are there any standards or conventions that suggest how to format ratings as ranges?

Example, what's the best format for the CONDITION rating?

0 to 5, zero is best
0 to 5, five is best
1 to 5, one is best
1 to 5, five is best


texts: migrating legacy databases from SQL Server 2000 via SQL Server 2008 to SQL Server 2012: users and logins

texts:
I have some legacy SQL Server 2000 databases to migrate to SQL Server 2012.

In my dry run, I have done this:

I created empty databases of the same names in SQL Server 2008.
Then I backed up the SQL Server 2000 databases and restored them, overwriting the 2008 databases.
Then I created empty databases of the same names in SQL Server 2012.
Then I backed up the 2008 databases and restored them, overwriting the 2012 databases.

At this point, I can log in to the SQL Server 2012 databases from within Query Analyzer, as Domain Admin using Windows Authentication, and query the databases; and SPs invoked from within QA work as they should.

The SQL Server 2012 server has been set up to support Windows and SQL authentication, but the SQL Authentication-mode connection strings from client applications do not work yet.
During the restore process, the users have been orphaned, as expected.

It has been a few years since I've had to migrate databases between server versions, so I cannot remember how to recreate the SIDs for these orphaned users, though there used to be a fairly quick way to do that, IIRC.   There was a script, and it didn't require me to do any preparation on the "from" server; it was simply run on the "target" server.

Please jog my memory.


texts:
My current understanding of the network-partitioning scenario, also mentioned in the CAP theorem, is that it's a scenario where the clients are not able to connect to some DB nodes even though they are up and running. For e.g. If a cluster consisted of say, 10 nodes, then it might happen due to some network issues that client A is able to connect to only 5 nodes, client B is able to connect to the other 5 nodes. In such scenarios, data inconsistency scenarios might arise and we need to plan for them.

As per my current understanding of partitioning, I don't see any such scenario arising when all the nodes are on the same rack/subnet. So this means that I don't have to plan for network-partitioning events(if I'm correct) as they can't arise.

Am I correct?


texts: Remove text from columns that may or may not be present multiple times

texts:
I have the following table in SQL Server 2008.

create table OrgUnits
(
    OrgUnitId int not null constraint PK_OrgUnits1 primary key,
    OrgUnitName varchar(100),
    OrgUnitLevel int not null,
    OrgUnitKey varchar(100) constraint UQ_OrgUnits_OrgUnitKey unique,
    Level1Id int not null,
    Level2Id int,
    StatusId int not null constraint FK_OrgUnits_Status references Status,
    VersionNumber int not null
);

INSERT INTO OrgUnits (OrgUnitId, OrgUnitName, OrgUnitKey, Level1Id, Level2Id,
                      StatusId, VersionNumber)
VALUES
(218824, 'Company1 (5) (5) (5)', '5', 218824, 1, 1),
(218825, 'Division1 (1) (1) (1)', '1', 218825, 1, 1),
(218826, 'XAVAT (4) (4) (4)', '4', 218826, 1, 1),
(218827, 'WATe (7) (7) (7)', '7', 218827, 1, 1),
(218828, 'Communications', '05-905-6320-300', 218824, 218828, 1, 1),
(218829, 'Corporate Development', '05-955-6320-300', 218824, 218829, 1, 1),
(218830, 'Corporate Board of Directors', '05-111-3515-301', 218830, 1, 1);

Ideally I'd have OrgUnitName appear like OrgUnitName + ' (' + OrgUnitKey + ')';
But it appears like that update has been run on some but not all the values in the table.

What would the update statement be to update all rows so they would have the correct format? Please note I have hundreds of rows and this is only to fix an earlier issue a previous update caused. The query only needs to be run once.


texts: Create view in a different database with dynamic SQL

texts:
As part of a larger project I'm trying to create a new view on each database in a SQL Server instance.

I've created a stored procedure that among other things is looping through the databases in the instance via a cursor then trying to create the view using sp_executesql.  Unfortunately the stored procedure does not exist in the databases that I am trying to create the views on.  So I'm trying to get sql server to use that database and then run the create view script.  When I run this I receive the error

  'CREATE VIEW' must be the first statment in a query batch.

Any ideas on how I can accomplish this?
Here is the sp_executesql portion of the procedure.

declare @DB VARCHAR(50)
declare @SQL NVARCHAR(max)
set @DB = '[dbname]'

SET @SQL =  N'USE ' + @DB + N' CREATE VIEW vNonhiddenCategories
AS
SELECT categories.categoryid
FROM categories
--WHERE ... irrelevant remainder of view code ...
'
execute sp_executesql @SQL


texts: Twice job results in sql server, last one is left in progress

texts:
I have the job MP - Create CSV which execute a package SSIS in sql server 2008, when I check for the job history I see the result at step 0, I expand and I have 2 results for the step 1.
Fist result have the icon for Success with the result :

  Message
  Executed as user: companyname\sa. The step succeeded.

The second one have the step 1 also, but with the icon for In Progress with the result :

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

  Started:  11:50:00 PM
  DTExec: The package execution returned DTSER_SUCCESS (0).
  Started:  11:50:00 PM
  Finished: 11:50:26 PM
  Elapsed:  26.645 seconds

My problem is I have a report to show all the job with their status not equal to 1 (success), my report do not work anymore because of this result "In progress".
Here is the sql I use to run my report :

SELECT *  from  msdb..sysjobhistory WHERE run_status != 1

My questions are why do I have 2 results for my step ? I think I should have only one. And what should I do when I have a job with a step status left "In progress" ?

For more information, my job is running daily and succeed every time, thank you.


texts: Can't connect to local Postgres databases

texts:
After trying to install Postgres using Postgres.app and EnterpriseDB, I decided to re-install it using Homebrew (As I got some problems connecting to database).

PYTHON=/usr/local/bin/python brew install postgresql
initdb -A trust /usr/local/var/postgres
initdb /usr/local/var/postgres -E utf8

mkdir -p ~/Library/LaunchAgents
cp /usr/local/Cellar/postgresql/9.3.2/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

I am working to create a database using Postgres.
Whenever I try to create database using command line, I got this problem :

MACBOOK:~ macbook$ postgres -D /usr/local/var/postgres

  LOG:  could not bind IPv6 socket: Address already in use HINT:  Is
  another postmaster already running on port 5432? If not, wait a few
  seconds and retry. LOG:  could not bind IPv4 socket: Address already
  in use HINT:  Is another postmaster already running on port 5432? If
  not, wait a few seconds and retry. LOG:  could not bind IPv6 socket:
  Address already in use HINT:  Is another postmaster already running on
  port 5432? If not, wait a few seconds and retry. WARNING:  could not
  create listen socket for "localhost" FATAL:  could not create any
  TCP/IP sockets

Or I face this problem

  Is the server running locally and accepting   connections on Unix
  domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

Any ideas to solve this problem?


texts: MySQL FullText Search returning Zero rows

texts:
I have a query that works really good on searches in my table. However, I came across this certain search that resulted in no rows, even though I just added these rows and I know they are there (about 50 of them that should match).

Query as follows:

SELECT MATCH(SoundTitle, FilePathAndName, Description) AGAINST('(gamer) ("gamer")' IN BOOLEAN MODE) as score,
    RowID, SoundTitle, FilePathAndName, Description, DatePublished
    FROM SoundEffects q1
    having score>0
    ORDER BY score DESC

Here's a sample of some rows returned using a like '%gamer%' query:

491     36      LittleGirlGamer/360 no scope_LittleGirlGamer.mp3        2019-01-17  0
492     36      LittleGirlGamer/Ask your gramma_LittleGirlGamer.mp3     2019-01-17  0
493     36      LittleGirlGamer/BRB_LittleGirlGamer.mp3     2019-01-17  0
494     36      LittleGirlGamer/Complaining_Aimbot_LittleGirlGamer.mp3      2019-01-17  0
495     36      LittleGirlGamer/Complaining_Hacker_LittleGirlGamer.mp3      2019-01-17  0

No, there are not 50% or more of rows in this table that would qualify for this search term. It would only be about 10% or so in the table.

The word gamer is not a stop word as far as I have read for InnoDB table.

Like I said, this query was working super great on other searches.

Thanks,
Dave


texts:
I wanted to do an export of a .bacpac file from MS Azure database, from local machine, with command like this:

sqlpackage.exe /Action:Export /ssn:tcp:<ServerName>.database.windows.net,1433 /sdn:<DatabaseName> /su:<UserName> /sp:<Password> /tf:<TargetFile> /p:Storage=File

The DB was apparently found and the tool reported it started extracting the schema from the database, but then it fails:

Extracting schema
Extracting schema from database
Time elapsed 00:00:05.17
*** An unexpected failure occurred: .NET Core should not be using a file backed
model..

To my surprise, I couldn't find anything similar on the web. Is this some kind of an MS Azure bug?


texts: Setting up relationships on a gaming schema with multiple locales

texts:
I've devised the following schema:

Where BotLocale holds locales like en-US, es-AR, etc. GameArtifact has a primary key which is artifact_id and GameClass with locale, identifier being the primary key.

How can I properly set up a relationship between the exclusive column of GameArtifact and identifier from GameClass taking into account that identifier will be repeated multiple times per row due to many locales?

This is how GameClass table looks like:

 id | locale | identifier  |      class
----+--------+-------------+------------------
    | en-US  | knight      | Knight
    | es-AR  | knight      | Caballero

One approach I thought for doing this is to separate GameClass into two tables, one which holds the identifier alone which would be referenced from GameArtifact and another table which holds the actual translations for a GameClass.

The issue in this approach would be that there's an extra table I'd need to JOIN when querying.

Is this the best way to achieve this or there's a better approach when handling this type of cases?


texts: When should extended events be used instead of SQL Profiler/perfmon?

texts: The extended events seem like a better technology and less stress on the server, but the SQL Profiler/perfmon has better tooling.  Also the extended events seem to have a steeper learning curve.  In which context should each be used?  Is it worth getting past the steep learning curve to take advantage of the extended events?


texts: ConfigServersInconsistent:How to correct the inconsistency?

texts: Detected that the one of the config servers is inconsistent with rest two, in a 3 config-server environment. But not sure how to set this right. I have gone through mongodb config servers not in sync and found that one of the chunks collection has a different dbhash compared to the other two. Will a plain replacement of the shards collection on this configserver work? Else, what needs to be done?


texts: slave server is down,master server query halts (postgresql)?

texts:
I have my master and slave server in sync.

I have stopped the slave server and tried to perform insert query in master server. The execution is in progress and doesn't give any error message. There is no proper information in logs.

I understand that in synchronous replication master waits for slave to reply but:

Is there anyway to avoid master server being halted?
I have stopped my master server and checked the logs that data is locally committed, is this the behaviour of synchronous replication? commit should not work until all data is committed to master and standby?


texts: Added a new listener to my 'listeners.ora' file. How can I connect directly to it with SQLPlus? (skipping tnsnames.ora for now)

texts:
I updated my listeners.ora file to include a new listener on port 1523. I then started the listener with lnsrctl start. I'd now like to connect to the database on that port via SQLPlus without specifying an entry in tnsnames.ora but I'm struggling.

So far, using the 'ezconnect method' I'm getting the following error:

SQL> connect system/Pa33w0rd@//localhost:1523/db2
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

And my listener.ora file looks like this:

# listener.ora Network Configuration File: C:\app\benfi\virtual\product\12.2.0\dbhome_2\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\benfi\virtual\product\12.2.0\dbhome_2)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\benfi\virtual\product\12.2.0\dbhome_2\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

LIST2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
    )
  )


texts: EF Code First uses nvarchar(max) for all strings. Will this hurt query performance?

texts:
I have some databases created using Entity Framework Code First; the apps are working and in general I'm pretty happy with what Code First lets me do. I am a programmer first, and a DBA second, by necessity. I am reading about DataAttributes to further describe in C# what I want the database to do; and my question is: what penalty will I be eating by having these nvarchar(max) strings in my table (see example below)?

There are several columns in this particular table; in C# they are defined as such:

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }
    public string Name { get; set; }
    public string Message { get; set; }
    public string Source { get; set; }
    public DateTime Generated { get; set; }
    public DateTime Written { get; set; }

I expect to query and/or sort based on Name, Source, Generated, and Written. I expect Name and Source to be in the 0-50 character length, occasionally up to 150. I expect this table to start pretty small (<100k rows), but grow significantly over time (>1m rows). Obviously message could be small or large, and will probably not be queried against.

What I want to know, is there a performance hit for my Name and Source columns being defined as nvarchar(max) when I never expect them to be larger than 150 characters?


texts: Slow updates of PostgreSql ByteA fields with just 1KB of data

texts:
TL;DR: Inserting or updating table, with putting 1024 bytes into bytea column works quite slow.

I have a PostgreSql 9.5.12 installation on my Desktop Linux Mint 18, with SSD drive, Core-i5, and 16GB Ram.

I've created table with the folowing structure:

CREATE TABLE "cells" (
  "x" integer NOT NULL DEFAULT 0,
  "y" integer NOT NULL DEFAULT 0,
  "palette_id" integer DEFAULT 1,
  "image" bytea NULL
);

It represents a "cells" on a 2D-field with 32x32 pixels image on it. "image" column always contain 1024 bytes, each byte is a color of one of the 32x32 pixels (each color is represended by a single byte).

So, every time, when I'm changing image and write it back to table, it takes about 450 milliseconds (almost a half of a second!).

I'm writing it with query like this (using php PDO):

UPDATE cells
SET image=decode('00000000000000000000000000000000000000000000000000000000000000000000002a2a4b6b6c4b4a2a2a4b00002a222a000000002222000000002200000000002a4b2a4b95b6956c2a4b95b6954b2a4b6c956c4b2a2a6b6c4b2a222a2a00004a4a4a2a4a4b4b4b4a222a4b734a2a2a2a4a4b4b2a222a4b4b2a2a222a4b2a7151312a2a2a2a5252292a2a5229312a2a2a2a5252292a2a5231312a2a2a2a317172524b2a2a4b4b4b4a2a2a4a4b4b2a2a2a4a4b4b2a2a2a2a4b4b2a2a2a2a4b71738d6d4b236d8e8e6d23236d8e6e6d23236d6e6e4c23236d6e6d4423236d6e7193aeaf4b4b8eb0b08643438eb0b08643438eb0876643438e8f8766234366877193b6af4b4baed8d08e4b4baed0d08e434baed0b08e434baed0b08e434366877193b6af6d6cafd8d0af6c6cafd0d0af6c6cafd0b08e646caed0af8e444466877173aeb08f8fafb0b0af8787afb0b08786868faf8f8766668f8f8787666666677173aed0b0b0b0b0b0b0b0b0a8a8a8a8a88787878787878787878787878767667193b6d0d0d0d0b0b0b0b0b0b0b0b0b0b0b0b0afafafafafa7a78787878767667193b6d0d0d0d0d0d0d0d0d0b0afafafafafafafafafaf8fafafafafaf8767667193b6d0d0d0d0d0d0d0d0d0afae6d6dafaf8d8dafaf6d6d8fafafafaf8767667193b6d0d0d0d0d0d0d0d0b0af8d4b4c8eaf6d6c8f8f6c648eafafafaf8767677193b6d0d0d0d0d0d0d0d0af8e6c23438eaf6d6daf8f6c648eafafafaf8767677193b6d0d0d0afaeaeaeaf8e6d4322438eaf6d6dafaf6d648eafafafaf8767677193b6d0d0af6d4c4b4b6c6c432222438eaf6d6dafaf6d6c8eafafafaf8767677193b6d0d0af4c2222222343222222438eaf6d6dafaf6d6c8eb0afafaf8787677193b6d0d0af6c2222222343222222438eaf6d6dafaf6d6c8eb0afafaf8787677193b6d0d0af6d4c4b4b6c6c432222438eaf6d6dafaf6d6c8eb0afafaf8787677193b6d0d0d0afaeaeaeaf8e6d4322438eaf6d6dafaf6d6c8eb0afafaf8787677193b6d0d0d0d0d0d0d0d0af8e6c234b8eaf6d6dafaf6d6c8eb0b0afaf8787677193b6d0d0d0d0d0d0d0d0d0af8d4b4cafaf6d6dafaf6c6c8eb0b0afaf8787677193b6d0d0d0d0d0d0d0d0d0afae6d6dafaf8d8dafaf8d6dafb0afafafa787677193b6d0d0d0d0d0d0d0d0d0d0afafafafb0afafafafafafafb0afafafa787677193b6d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0b0b0b0b0b0b0afafafa7876700b5b6d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0b0b0b0b0b0b0afafafaf876700f8d8d0b0b0b0b0b0b0b0b0afaeaeaeaeaeaeaeafd0b0b0b0b0b0afaf87876700f8d8b0906868686868688f9493937373737394b7d0d0b0b0b0b0af8787876700000000686848484848486e9391717171717193b6d0d0b0b0b0b0af87676700', 'hex')
WHERE id=2941

I've also tried to use the E'\\x escaping instead of "decode" function.

If I run this query from the psql console, it takes about 0.25 second, which is faster whan php's query, but not dramatically.

I've googled for "bytea performance issues" and found, that people have problems with importing like 100MB of data into a table column, not the 1kb.

Also, for comparision, in other table I'm inserting an array of 256 char items (which are 9 bytes long each), and it takes about 0.00156 second.

So:

1024B to bytea takes 0.25 seconds

2304B to char[9][] array takes 0.00156 seconds

How could it be? What should I do? Should I use text datatype instead of bytea? Or maybe an array of bytes would be more efficient?


texts: How to identify the origin of a invalid connection with sql1060

texts:
I am getting a message similar to the next one:

2007-08-08-09.28.41.745208-240 I10292373A694 LEVEL: Error
PID : 1720384 TID : 1 PROC : db2agent (DPADB) 0
INSTANCE: XXXXX NODE : 000 DB : DPADB
APPHDL : 0-154 APPID: GA381C80.O107.00D748132841
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1060 sqlerrml: 8
sqlerrmc: YYYYY
sqlerrp : sqlrrait
sqlerrd : (1) 0x8012006D (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:

I want to know from which machine the connection is being tried.
Do I have to use a sniffer, or does DB2 have a tool to identify this point?


texts: Most suitable database design for multi store inventory?

texts:
I am trying to understand how to manage multiple stores in a inventory management system (Each store has large number of data). There are 123 stores in this shop. Some stores have sub stores.

So I planned to create one master database (only super admin can access this) and one separate database for each store and database user for each database.

I have some doubts about this approach.

1). Is this design is correct? If not what are the suitable ways?

2). What is the best DBMS for this kind of projects?

3). How to update master database to get daily sales, monthly sales, employee details etc real time?

4). How many databases I can create using this