texts: Some SQLs sometimes take all available RAM

texts:
I'm running postgresql server on Windows Server 2008, which has 6GB of ram. My dataset is something near 1,5GB big.

I have problems, when I run specific SQLs in table, which has 51mio of records. The fields which I use in SQLs are indexed (alarm, id_elektrarna, id_razsmernik). Below is the ANALYZE SQL, which causes problems. Do you have any suggestions what to do?

UPDATE:

When I run my query, I see how memory of the system where database is running is increasing, while some scripts that are using database just "pause" working. When I stop SQL from running, everything works again.

There are additional data:  #work_mem = 1MB (commented), shared_buffers = 32MB,

index definition, table definition, SQL that is the issue: http://pastebin.com/4iMtpD4r

The database server is using the RAM, not the client application. This is the SQL with EXPLAIN (ANALYZE,BUFFERS) that executes in less than 1sec and do not cause problems. Same SQL, different UNIT in database (different ID) takes more than 15-30minutes: http://explain.depesz.com/s/OdqU.


texts: Jdbc3SimpleDataSource vs. PGSimpleDataSource. What's the difference?

texts:
PostgreSQL's JDBC 4.2 driver includes classes PGSimpleDataSource and Jdbc3SimpleDataSource.  The latter extends the former, but there's no difference in their APIs.  And I find that both provide the same Connection object.

I assume that there must be some difference in their implementations.  But what is it?  Under what conditions should we prefer one implementation over the other?

Unfortunately, the documentation of Jdbc3SimpleDataSource does not provide any details.  Any hints?


texts: Audit Trail in NoSQL vs. Relational DB, audit trail feature in MongoDB

texts:
Two questions regarding selection a DB type and engine for the scenario when the Audit Trail functionality is a must:

Is there any preference in DB type (NoSQL/Relational) choice when the Audit Trail is critical?
Is it a common practice to use a MongoDB in a scenario when the Audit Trail is an obligatory feature?

As far as I know, MySQL and SQL Server offer such functionality, but I don't find such option in MongoDB features list.


texts: Does PostgreSQL support wildcard indexes on JSON?

texts:
I have a multi-tenant service with a table like this:
project_id | user_id | user_properties

Each project belongs to a different customer and customers can freely attach metadata to their users. A project may have millions of users.
Then a customer may want to find some users inside his project filtering with the user_properties (e.g. age grater than X, favorite music equal to Y, etc.)
user_properties can be an arbitrary json of key-value pairs and a customer can run arbitrary queries on the user_properties. The json is not nested (only key-value pairs).
Since a query may return many results it would also be useful to use some sort of pagination (e.g. order by user_id + limit). But pagination, together with arbitrary filters, seems an additional issue for performance...
Is it possible to handle that case in PostgreSQL? Is EAV the only solution?
I see that MongoDB supports wildcard indexes: does PostgreSQL offer anything similar?


texts: Is there any reason I shouldn't delete orphaned database users?

texts:
I've inherited a business application which has been around for c. 15 years composed of multiple databases that are all hosted on the same SQL Server (2012 SP4) instance.

In all of the databases there are SQL Server database users (not ones that are related to Windows Users or Groups) that are not mapped to any server login. These have accreted over the time the system has been in use. Specifically database users here not server logins. My suspicion is that they've been orphaned over time as the databases have been migrated from one server to the next over the last 15 years.

Some of these users are members of the db_owner database role and have thus been flagged up by sp_blitz. Whilst change control and approval are important; they're not the main thrust of this question. I identified the orphaned users due to sp_blitz flagging them because they have db_owner rights; the fact that they have db_owner is tangential to the core question here.

No database or agent jobs are associated with them - for the most part they look like utility accounts that were created for various tools and solutions which were subsequently deprecated or superseded - which explains why they didn't get re-created as server logins when the databases moved from one server to the next.

As best I can tell from my research, these users are entirely inert and won't have any access to the system (due to not being associated with a login), so: Is there any reason I shouldn't remove these users?


texts:
For the distributed queries, is it possible to determine the server that sent a query?

For example, I have stats:

SELECT DB_NAME(st.dbid),
       text,
       request_time,
       query_plan,
       (requested_memory_kb / 1024),
       (granted_memory_kb / 1024),
       query_cost
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS ph
ORDER BY requested_memory_kb DESC

How can I get server that sent each query? What tables should I join or functions to apply?


texts:
How is it possible to execute a query as a applicationintent=readonly using powershell?

I have an cluster alwayson environment.
I want to call a procedure with invoke sqlcmd to the readonly database (secondary). I have the listener name and the database name.


texts: Cannot connect PHP to SQL Server in no way

texts:
I installed SQL Server and it shows enabled in phpinfo(). But when setting up joomla on database config page when I choose SQL Server and try to connect the following error comes up

  Could not connect to the database. Connector returned number: Database sqlsrv_connect failed, Array ( [0] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 67 [code] => 67 [2] => [Microsoft][ODBC Driver 13 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [67]. [message] => [Microsoft][ODBC Driver 13 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [67]. ) [1] => Array ( [0] => HYT00 [SQLSTATE] => HYT00 [1] => 0 [code] => 0 [2] => [Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired [message] => [Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired ) [2] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 67 [code] => 67 [2] => [Microsoft][ODBC Driver 13 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [message] => [Microsoft][ODBC Driver 13 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. ) )


texts: Querying the most recent records with two fields: datestamp and timestamp

texts:
I have a table called airquality that has records for air quality measurements taken on different days and times within those days. These date/timestamps are stored in two different columns: validdate and validtime. I can query either one of those fields to return me the maximum value with the following query:

select *
from airquality
where validdate = (select max(validdate) from airquality)

This returns me the correct rows for the most recent day (format: 05/02/20). But how can I modify this query to also check for the latest time in the validtime column (format: 16:00)?

I tried the following:

select *
from airquality
where validdate = (select max(validdate) from airquality)
and validtime = (select max(validtime) from airquality)

But this doesn't work because I can have timestamps for 05/01/2020 that have a timestamp that 05/02/2020 doesn't have yet. (Example: 05/02/20 16:00 vs 05/01/20 23:00)

I am on Postgres 12.2

Any suggestions or ideas?
Thanks in advance.


texts: pg_dump of table and its transitive dependencies

texts:
Sometimes I'd like to export just a single table and its dependencies, so I can do some experiments with it on a test database. Is there a way to do that, other than dumping the full db schema with pg_dump and any table dependencies manually?

For example let's say I have a table employees which has a single foreign key to companies.id and uses a few custom enums or other types. Let's also assume companies is self-contained, i.e. does not depend on anything else. So what I'd like to have is some way of dumping  employees, both schema and all (or a subset) of the data and automatically get the required type definitons, and the schema for company as well as all referencered entries.

Is there a way to do this easily with pg_dump or some other tool?


texts: Should Postgres USER/ROLE management be done with Flyway?

texts:
I've recently inherited a company database. The current practice is to put everything schema-related in a Flyway migration and obviously we never change the schema outside of Flyway. This includes USER and ROLE operations but does it need to?

Will creating/altering roles outside of Flyway break migration continuity?

I'm wondering if we are unnecessarily hampering ourselves by forcing every role change to be made through Flyway.


texts: How does one investigate the performance of a BULK INSERT statement?

texts:
I am mainly a .NET developer using Entity Framework ORM. However, because I don't want to fail in using the ORM, I am trying to understand what happens within the data layer (database). Basically, during the development I start the profiler and check what some parts of code generate in terms of queries.

If I spot something utterly complicated (ORM can generate awful queries even from rather simple LINQ statements, if not carefully written) and/or heavy (duration, CPU, page reads), I take it in SSMS and check its execution plan.

It works fine for my level of database knowledge. However, BULK INSERT seems to be a special creature, as it does not seem to produce a SHOWPLAN.

I will try to illustrate a very simple example:

Table definition

CREATE TABLE dbo.ImportingSystemFileLoadInfo
(
    ImportingSystemFileLoadInfoId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_ImportingSystemFileLoadInfo PRIMARY KEY CLUSTERED,
    EnvironmentId INT NOT NULL CONSTRAINT FK_ImportingSystemFileLoadInfo REFERENCES dbo.Environment,
    ImportingSystemId INT NOT NULL CONSTRAINT FK_ImportingSystemFileLoadInfo_ImportingSystem REFERENCES dbo.ImportingSystem,
    FileName NVARCHAR(64) NOT NULL,
FileImportTime DATETIME2 NOT NULL,
    CONSTRAINT UQ_ImportingSystemImportInfo_EnvXIs_TableName UNIQUE (EnvironmentId, ImportingSystemId, FileName, FileImportTime)
)

Note: no other indexes are defined on the table

The bulk insert
(what I catch in profiler, one batch only)

insert bulk [dbo].[ImportingSystemFileLoadInfo] ([EnvironmentId] Int, [ImportingSystemId] Int, [FileName] NVarChar(64) COLLATE Latin1_General_CI_AS, [FileImportTime] DateTime2(7))

Metrics

695 items inserted
CPU = 31
Reads = 4271
Writes = 24
Duration = 154
Total table count = 11500

For my application, that's ok, although the reads seems rather large (I know very little about SQL Server internals, so I comparing to the 8K page size and the small record information I have)

Question: how can I investigate if this BULK INSERT can be optimized? Or it does not make any sense, since it is arguably the fastest way to push large data from a client application to SQL Server?


texts: Create SQL Server trigger before insert/update

texts:
I have a table with columns (a, b, c, d) and I would like to ensure that the 4-uplet a,b,c,d is unique for each row of the table by using SQL server triggers.

I basically would like to know how I can use triggers to check that the data that is about to be inserted/updated will not break the property I mentionned above.

So in case of an insert attempt, I would like to execute a SELECT on the table to check if the 4-uplet that's about to be inserted already exist and return an error value in this case. I would like to act similarly for updates.

EDIT: Actually my starting point is that I have an oracle SQL index that I want to convert to SQL Server in the context of a database migration. The Oracle SQL index creation structure is as follows:

CREATE UNIQUE INDEX MY_SCHEMA.MY_UNIQUE_INDEX ON MY_SCHEMA.MY_TABLE
(
    CASE "X_ID" WHEN 2 THEN "COL_A" ELSE NULL END ,
    CASE "X_ID" WHEN 2 THEN "COL_B" ELSE NULL END ,
    CASE "X_ID" WHEN 2 THEN "COL_C" ELSE NULL END ,
    CASE "X_ID" WHEN 2 THEN "COL_D" ELSE NULL END
);

This index seems to impose unicity of (col_a, col_b, col_c, col_d) 4-uplet that's why I chose to translate it by an SQL Server trigger because functional indexes don't exist in SQL Server.


texts: Is it safe to use "Safety" as a schema name?

texts: I was considering using "Safety" as a new schema name in SQL Server. In SSMS, the term Safety is colored as a keyword, but this term is not listed as a reserved keyword in the BOL. The database does not complain if I create and use it, but is there any reason why I should avoid using it?


texts: What's an example of a Distributed Database Query?

texts:
Since I may not come across or get a chance to use a distributed database system. I would like to understand an example of a Distributed Database Query.

I know that in a distributed system, you have database stored on multiple computers connected via network.

Basically, I want to know on what level the data is kept on different computer

Is it on table-level, database-level etc.?
a part of some table is kept on "Computer A" and another part of table is on "Computer B"?

If so then Querying the table will take fetching the data from multiple computers.


texts: Disabled index still incurs overhead when inserting

texts:
I have a 38 million row table that I periodically truncate and refill.  I disable the indexes (non-clustered) when I do this.  But when I included the Execution Plan I noticed SQL Server 2005 is still updating the indexes as I insert the data.  How can this be?

Paul White has asked for some documentation.  I created a test script to simplify the event.  In summary, this is what I found.  Consider a table with a clustered index and 2 non-clustered indexes.  If the 2 non-clustered indexes are disabled, they are ignored when inserting data as expected.  But if the clustered index is also dropped, then the 2 non-clustered indexes ARE updated during the insert.   I am only allowed 2 links so I included my DDL script and the execution plan that shows the disabled indexes being updated.

IndexTesting4Inserts.sql
IndexesOnLine.sqlplan
IndexesDisabled
ClusteredIndexDropped


texts: Emulate REGEXP like behaviour in DB2 SQL

texts:
I posted the same on stackoverflow (please, let me know if I have to delete one).

I'm working on a DB2 database and as far as I can see regexp is not supported (without additional libraries).

So I cannot implement something similar to what is explained in this article "Bringing the Power of Regular Expression Matching to SQL"

Do you know if I can "emulate", with a SQL statement, a regular expression like this?

  ^a[aofdmep]{1}[a-z]{1}[a-z0-9]{4}[sidbfkfpo]{1}

Edit

In the above hypothesis I found that it's acceptable for my case a WHERE Like predicate:

  WHERE USER_NAME NOT LIKE 'a_______'

But it's a unsafe and doesn't cover other cases where I don't have a fixed char that I can match.


texts: How do you clear out all old query plans from within Microsoft SQL Server?

texts:
We have an off the shelf application that uses a Microsoft SQL database. Within this application we pick and choose various selection criteria for each report. This application then runs these reports.

I believe we have a query plan issue. The first report we run each day, runs very fast 7 minutes. Any report we run after the first report takes over an hour.

Each night we run a scheduled task that stops and starts SQL Server Agent and SQL Server. There are approximately 25 other databases within this one instance of SQL Server. No other databases have performance issues, only the one off the shelf product I mentioned earlier.

Is there a way to clear out all query plans that SQL Server currently has in memory?

How can I do this without impacting 30 or so users that rely on other databases on the same server?


texts: SQL Server Agent fails to start in Failover cluter

texts:
The SQL server agent in a cluster (as a cluster resource) fails to start. I'm looking for error logs to give me a clue, but so far, all it says is: "failed to start". What could be the possible issues? Where can I see a more detailed error log?

Update: I've checked the sql server logs, the cluster event logs and the windows event logs.


texts: Build dynamic text search on table columns

texts:
With no success, I am trying to build a dynamic search text on a table with two columns (Id & Number, both columns are unique).

The Number column should be grouped and start a new section of search (with OR). The Id column should be separated with "," (with IN).

Example (Please ignore the namings, I simplified my case and changed the names):

CREATE TABLE #MyTable
(
    Id INT,
    Number INT
);

INSERT #MyTable VALUES (6, 3), (7, 3), (8, 3), (16,15), (20, 15);

Expected result: @Query VARCHAR that will contain this:

'
    SELECT  Id, Number
    FROM    #MyTable
    WHERE   (
                Number = 3
                AND Id IN (6, 7, 8)
            )
            OR
            (
                Number = 15
                AND Id IN (16, 20)
            )
'

Note: The reason I am doing it dynamically because I do a dynamic filters search.

I guess the solution should use FOR XML PATH('') and STUFF() methods and probably also a GROUP BY, but I am not managing to do it (I succeeded to build a dynamic WHERE on one column but facing lots of difficulties in doing it with 2 columns. Actually, I can build it with a loop but I guess it could be done much better and therefore I ask you for a help.

Thanks in advance.


texts: New tables not showing up in MySQL Workbench Navigator

texts:
I've created a database on a local MySQL server with MySQL Workbench version 8.0 on Windows 10, but when I make a table in said database with a manually typed query

CREATE TABLE newtable (column1 INTEGER NOT NULL, column2 VARCHAR(30), PRIMARY KEY column1);

I can't see the table in the Navigator panel on the left hand side, even after right-clicking the schema and clicking Refresh All. However, the query was completed successfully according to the output. If I right-click the database and open the Schema inspector, in the Tables column, I can see the new table. I can even work with the new table with typed queries - I just can't seem to open the GUI. That is, until I restart Workbench, and then it shows up.

I tried creating a new table using the GUI, that is, right-click on "Tables" in the Navigator and then creating a new table, and that works as expected and the new table is visible in the Navigator.

Is this a bug in MySQL Workbench or am I doing something wrong? (I am very new to this, I'm still learning!)

EDIT: After rebooting my computer, the problem is gone and refreshing schemas works as expected. ¯\_(ツ)_/¯


texts: Can't access MySQL database with created user through dll

texts:
I have a web service that connects to a MySQL database from a dll, both of them are on the same machine. If I use the root user and password it will connect no problem but if I use a user that I created it comes back with

Access denied for user '####'@'%' (using password: YES).

These are the commands I used to create the user

CREATE USER '####'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO '####'@'%';

My understanding from these commands is that the user can connect to the database from any location. Since I kept getting that error message every time I connected I then thought that '%' didn't include 'localhost' so I then created the same user with these commands

CREATE USER '####'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO '####'@'localhost';

This also didn't work, just the same error message only this time with 'localhost' in place of '%', then I thought that since the root user works I'll copy all the user hosts it has, so I used these commands

CREATE USER '####'@'127.0.0.1' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO '####'@'127.0.0.1';
CREATE USER '####'@'::1' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO '####'@'::1';

I'm connecting to the same database for both users, I'm 100% sure the password is correct for both of them and I'm connecting by localhost. I can connect to the database with no problems through MySQL Workbench and the MySQL Client using the created user. There must be some permission I haven't given to the created user, but I don't know what, could anyone help me solve this?

I issued FLUSH PRIVILEGES; after creating all the users. skip-networking is off and I granted all privileges to the new user and I was able to access the database. I've created an exe program to connect to the same database, with the privileges removed and it worked. I don't understand why the exe works but not the dll. I'm going to try and grant privileges one by one, which might narrow down the problem.


texts: How many rows can a SQL Server 2012 table hold, and when should I partition it?

texts:
Say a table stores granular data about some event. It has the date of the event, a type dimension with around 30K types, and a category dimension with around 100 categories, plus some numeric facts.

On average, there are 15 million transactions per day. More than 5 billion per year, over 60G per decade. That's not big data, but it's a lot.

How many rows can a SQL Server 2012 table hold?

Of course, older data is used less often and is candidate for being partitioned in multiple tables on the same DB. But when should this partitioning start happening? 1 table per year? 5 years?

Additional information collected from comments:

Consider: I have enough storage to hold 30 billion records of that event. If each event record requires 1KB, I have 30TB in that table, and enough storage for that (and for log of it). Its PK is bigint.

What do you think of having one table with historical data and another table with latest data? Instead of a transactional event, the table has a catalog, clients, for example. Every day the OLTP's catalog is copied into DW. So I'd have a table holding the history data and another table with the latest records.

In the design I use, ETL feeds the history table, then I use row_number() to grab the latest record of each entity by its NK. It's very expensive to run, but this way I keep entities that existed in the past and aren't on the OLTP anymore.


texts: Test if a string is a palindrome using T-SQL

texts:
I am a beginner in T-SQL. I want to decide whether an input string is a palindrome, with output = 0 if it is not and output = 1 if it is. I am still figuring out the syntax. I am not even getting an error message. I am looking for different solutions and some feedback, to gain a better understanding and knowledge of how T-SQL works, to become better at it --I am still a student.

The key idea, as I see it, is to compare the left- and right- most characters to each other, to check for equality, then go on to compare the second character from the left with the 2nd-from last one, etc. We do a loop: If the characters are equal to each other, we continue. If we reached the end, we output 1, if not, we output 0.

Would you please critique:

CREATE function Palindrome(
    @String  Char
    , @StringLength  Int
    , @n Int
    , @Palindrome BIN
    , @StringLeftLength  Int
)
RETURNS Binary
AS
BEGIN
SET @ n=1
SET @StringLength= Len(String)

  WHILE @StringLength - @n >1

  IF
  Left(String,@n)=Right(String, @StringLength)

 SET @n =n+1
 SET @StringLength =StringLength -1

 RETURN @Binary =1

 ELSE RETURN @Palindrome =0

END

I think I am on the right track, but I am still a long way off. Any ideas?


texts: MSSQL: Generate a 8 digit serial/Barcode number from a prefix number

texts:
I have a complicated task ahead of me and I really could use some of your brains to help me out.

At my work we have a database where we store barcode digits. The table contains a 6 digit as a prefix for an 8 digit barcode, were we currently manually calculate the last 2 digits of the barcode in Excel.

Here are the steps of how we manually calculate the last 2 digits:

Barcode prefix example:

declare @prefix int
set @prefix = 573071

1) Each prefix should be generated 10 times, where the 7th digit auto increments from 0 to 9

5730710
5730711
5730712
5730713
5730714
5730715
5730716
5730717
5730718
5730719

2) After adding the 7th digit, we use the following formula to calculate the 8th digit for each row:

First we start calculating the first row((5730710). Then we begin with calculating the first, third, fifth, seventh digit and multiply by 3 and add them together

5730710:
5 x 3
3 x 3
7 x 3
0 x 3

Then we take the second, forth, sixth digits and mutiply by 1 and add them together:

7 x 1
0 x 1
1 x 1

We add both results using SUM (45 + 7 = 53)
Then we round up 53 to nearest 10 (upwards only) in this case it has to be 60

DECLARE @num int = 53;
SELECT @num + (10 - (@num % 10)); = 60

After we round up we subtracts the round up value with the original value:
(60 - 53 = 7)

....And finally we have generated an 8 digit barcode from a 6 digit prefix:
    Result: 57307107

So my question is, how do i do this through sql?

Here is how I am calculating the 8th digit:

declare @prefix int
set @prefix = 5730710
Select
(
SUBSTRING(cast(@prefix as varchar), 1,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 3,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 5,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 7,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 2,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 4,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 6,1 )*1
)
+
(10 - ((
SUBSTRING(cast(@prefix as varchar), 1,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 3,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 5,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 7,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 2,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 4,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 6,1 )*1
)%10))

-
(
SUBSTRING(cast(@prefix as varchar), 1,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 3,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 5,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 7,1 )*3
+
SUBSTRING(cast(@prefix as varchar), 2,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 4,1 )*1
+
SUBSTRING(cast(@prefix as varchar), 6,1 )*1
)

But I'm still missing how to auto generate the 7th digit from 0 to 9 and put it into the above formula,
and lastly once the 7th and 8th digits are calculated I need them to be added to the prefix to finally
see the final result of 8 digit barcode.


texts:
I am trying to run xampp on linux through terminal, but mysql database is not running
after running the command to start Xampp server I get following output
Starting XAMPP for Linux 8.0.2-0...
XAMPP: Starting Apache...already running.
XAMPP: Starting MySQL...ok.
XAMPP: Starting ProFTPD...already running.
root@kali:/opt/lampp# /opt/lampp/bin/mysql.server: 264: kill: No such process

Upon checking the mysql status I get this output
● mysql.service - LSB: Start and stop the mysql database server daemon
     Loaded: loaded (/etc/init.d/mysql; generated)
     Active: inactive (dead)
       Docs: man:systemd-sysv-generator(8)

How should I resolve it?


texts:
I have a table called devices. Most of the devices that will get stored in this table can be uniquely identified by their serial number and part number. But there are some device types that do not have serial number and part number assigned to them. Instead they can be uniquely identified by another field (internal id).

Should I create a surrogate key for this table or should I create a composite primary key (serial number, part number, internal id) and insert default values to the serial number and part number columns when they are not supplied? The device types that do not have part number and serial number now, will have the numbers assigned to them in the future releases (may be 5 years from now). Should I create a surrogate key or a composite key in this scenario? Or using the three unique attributes, should I create a hash in the program and use that as a surrogate key for the tables?


texts: Extremely slow datapump imports from 10.2.0.1.0 -> 11.2.0.1.0

texts:
All imports/exports talked about here are using datapump, the 10.1.5 system is running on Windows Server 2003 and the 11.2 system is running on Linux.

I have an 11gig datapump dumpfile from a 10.1.5 database that I need to import into a 11.2.0 database and it's taking about 6 hours - much longer than I would expect. Once the data is imported, further exports/imports of the same schema on 11.2 run much faster - typically in about 25 minutes.

The import takes about between 5h30m and 6h30m hours on a reasonably high powered server. We've tried on a few different machines running Centos and Oracle Linux, with raided disks and with SSD's and nothing seems to have much impact on the time. (I've also tried various datapump options like parallel, or direct path with no impact).  The logs look normal with no errors presented - it's just extremely slow.

The slowness occurs during the actual table data loading, rather than the index or constraint creation at the end.

Once I have the data imported, I can export it in about 25 minutes, and import it back in about the same and the file sizes produced by these exports are about the same size as the original but just load 5 times quicker.

The most likely explanation I can think of for this is that the datapump format has changed between 10 and 11 and there's some sort of conversion going on when I'm doing the initial import.

Does anyone have any ideas on what's going on? Or how I can further investigate what's happening to cause this?

Import command:

impdp xxxx/yyyy@zzz directory=backups dumpfile=PROD_AUG2015.DMP   EXCLUDE=TRIGGER,CONSTRAINT,GRANT

Thanks!

(Mostly irrelevant background info - this is for a time sensitive upgrade for a client on a remote island with fairly crappy internet. The data is coming from a system we have restricted access to and going into our own database. Due to the restricted access to their current production system it is a major exercise to make changes in the source database so trying to avoid that if possible)


texts: Notification on long-running query or deadlock in SQL Server 2008 R2?

texts:
I'd like to know if there is a way to send a notification on deadlock? If so what queries would be required. I understand that SQL Server takes care of deadlocks, I simply would like information on the queries involved.

I found the following to determine long-running queries:

SELECT
    creation_time
    ,last_execution_time
    ,total_physical_reads
    ,total_logical_reads
    ,total_logical_writes
    , execution_count
    , total_worker_time
    , total_elapsed_time
    , total_elapsed_time / execution_count avg_elapsed_time
    ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset END
    - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where total_elapsed_time >= 300000000 --5 min
ORDER BY total_elapsed_time / execution_count DESC;

I'd like to know if the above is the right way to go, or is there a better way to determine if any query takes longer than a specific interval say 5 min as shown?

Thanks


texts: MySQL Replication - Issues with Triggers

texts:
MySQL master has a table   Products that has triggers on update and delete.  Deleted/Updated records are stored in ProductsUpdatedDeleted tab.

When a record is deleted, the operation is replicated to the slave server.   The issue is that slave also executes the trigger thus adding a record in its ProductsUpdatedDeleted table.  Then a duplicate record in ProductsUpdatedDeleted received from the master server through replication.

What is the best way to stop the slave to execute triggers?  I can comment trigger code in the slave but I would prefer both servers in the same state to quick failover.


texts: How save psql `ERROR` output to the same file as the DDL commands?

texts:
I'm trying to restore a database from a text file. I'm following the answer on this question. How do I restore a plain text postgres .backup file

But add: > c:\result.txt

psql.exe -U  sde sde < c:\backup\sde_04_06_2018.backup > c:\backup\result.txt

This seem to work but doesn't write the errors.

You get messages like this on the text file:

REVOKE
GRANT
SET
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES

But on the command windows you see error like this:

ERROR:  constraint "sde_layer_stats_version_id_fkey" for relation sde_layer_stats" already exists
ERROR:  constraint "xml_columns_fk1" for relation "sde_xml_columns" already exists
ERROR:  constraint "xml_columns_fk2" for relation "sde_xml_columns" already exists
ERROR:  constraint "xml_indextags_fk1" for relation "sde_xml_index_tags" already exists

The idea is save the error on the text file so I can review and decide if are something I need to handle.


texts:
I have the following query:
SELECT "account_transactions"."subsidiary_id"
FROM "account_transactions"
WHERE (transaction_type = 'loan_payment'
        AND amount > 0
        AND status = 'approved')
        AND (DATE(transacted_at) >= '2020-08-01'
        AND DATE(transacted_at) <= '2020-08-31'
        AND subsidiary_type = 'Loan')
ORDER BY  transacted_at ASC

I identified that it was considered a slow query so I'm trying to optimize it by applying an index. However I tried variations of indices such as putting an index on (transaction_type, amount, status) WHERE amount > 0 AND status='approved' AND transaction_type = 'loan_payment' or an index on transaction_type only. But when I run EXPLAIN ANALYZE, it still shows that it's running Seq Scan meaning that indices aren't being used.
Is this a PostgreSQL thing or am I doing indexing wrong?


texts: SQL SERVER equivalent for Oracle DBA_USERS

texts:
I'm working on conversion of Oracle queries to MSSQL queries. Following is query for Oracle -
SELECT USERNAME FROM DBA_USERS WHERE USERNAME = 'TESTUSER';

But in MSSQL, DBA_USERS is invalid object is thrown. Please suggest what is equivalent of the same in MSSQL.


texts: NULL values on MDX query (Total Column has Values)

texts:
I'm a novice with MDX, so sorry if I've missed something. (and sorry about my english too!)

The following query:

WITH
 SET [ROWMEMBERS] AS '{([BIN_SCH_INV_GRO].&[1]) , ([BIN_SCH_INV_GRO].&[2]) , ([BIN_SCH_INV_GRO].&[3]) , ([BIN_SCH_INV_GRO].&[4]) , ([BIN_SCH_INV_GRO].&[5]) , ([BIN_SCH_INV_GRO].&[6]) , ([BIN_SCH_INV_GRO].&[7]) , ([BIN_SCH_INV_GRO].&[8]) , ([BIN_SCH_INV_GRO].&[9])}'
SELECT {([Measures].[BIN_SCH_REC_VAL], [BIN_SCH_Year].&[2010])
 , ([Measures].[BIN_SCH_VAL_Real_CSP], [BIN_SCH_Year].&[2010])
 , ([Measures].[BIN_SCH_REC_VAL], [BIN_SCH_Year].&[2011])
 , ([Measures].[BIN_SCH_VAL_Real_CSP], [BIN_SCH_Year].&[2011])
 , ([Measures].[BIN_SCH_REC_VAL], [BIN_SCH_Year])
 , ([Measures].[BIN_SCH_VAL_Real_CSP], [BIN_SCH_Year])
 } ON COLUMNS, [ROWMEMBERS] DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON ROWS FROM [ProductsManagement]

Returns this:

BIN_SCH_REC_VAL BIN_SCH_VAL_Real_CSP    BIN_SCH_REC_VAL BIN_SCH_VAL_Real_CSP    BIN_SCH_REC_VAL BIN_SCH_VAL_Real_CSP
    2010    2010    2011    2011    ALL ALL
ALIMENTACAO (nulo)  (nulo)  (nulo)  (nulo)  2,663,657.70    1,650,353.73
ALIMENTACAO PARENTAL E ENTERAL  (nulo)  (nulo)  (nulo)  (nulo)  1,028,281.08    1,033,596.66
BRINDES (nulo)  (nulo)  (nulo)  (nulo)  163,867.06  187,183.59
EQUIPAMENTOS DE SEGURANCA   (nulo)  (nulo)  (nulo)  (nulo)  497,320.63  372,173.12
FIOS CIRURGICOS (nulo)  (nulo)  (nulo)  (nulo)  1,280,577.64    1,288,735.81
GASES MEDICINAIS    (nulo)  (nulo)  (nulo)  (nulo)  144,900.20  141,914.26
HIGIENE E LIMPEZA   (nulo)  (nulo)  (nulo)  (nulo)  1,895,881.03    1,949,619.64
IMPRESSOS   (nulo)  (nulo)  (nulo)  (nulo)  1,617,042.25    1,652,345.87
LABORATORIO (nulo)  (nulo)  (nulo)  (nulo)  6,672,048.90    6,725,001.30

I'm pretty sure that those rowmembers contain values on the specified years...

Any Ideas?

Thank you!


texts:
I have created a user on my primary node that has access to all of the databases in Availability Group AG1.  How do I ensure that same user has the same rights on all the other replicas in AG1?

According to this MSDN article (http://msdn.microsoft.com/en-us/library/hh270282(v=sql.110).aspx) I need to determine if they are contained databases first but I'm not entirely sure I have contained databases.  The user that I created on the other replica's get's an error when I try to add him to the AG1 databases.  The error indicates they are part of an Availability Group and are not accessible.


texts:
Let's say I have a table with 50,000 transactions that need to be processed in the background in the same order they were received. I have a Windows service that polls for transactions, picks them up, updates the transaction table that that record is picked by this instance at a particular time. All this works perfectly well.

Now, suppose I want to scale out the processing and run another instance of the same service on another machine, how do I ensure that both instances do not pick up the same records?

I  either have both instances picking the same record and one instance overwriting update of the other or if I have additional filters in the update query to prevent that I have one instance picking up some records and updating it and the other instance running but not able to do anything simply because the record is already updated.

Is there a way to solve this?


texts: Consideration for a little bit heavy database

texts:
Environment information

OS : Windows Server 2012 R2 ( 64bit )
RAM : 16.00GB
CPU : Intel(R) Xeon(R) CPU E5-2609 @ 2.40GHz
SQL : Windows SQL Server 2012 Standard

Brief Database Data Information

Among more than 10 tables, one of them has varbinary(max) type column
The table has more than 1m records and each column has thumbnail data that takes up about 20k

Brief Table Spec

Table name : RegisteredThumbnail
Column name : UserId, ThumbData, Created, Updated
Column type : int, varbinarymax, datetime, datetime
Used space info : row : 1,034,300 | reserved : 34,092,160 KB | data : 34,054,872 KB | index_size : 31040 KB | unused : 6248 KB

Query
SELECT * FROM RegisteredThumbnail WHERE UserId = 512315

This query takes about 6:45 minutes to fetch the expected row.
To overcome this, is indexing the only option for me?
Changing the way image data is stored by replacing the binary data to image url as a string data will help a lot?
Since this is a currently operating system, changing the column is not a good idea though.
Any ideas would be greatly appreciated.
No indexes have been configured yet.


texts: How can I find out what my SqlServer 2005 CPU is mostly working on?

texts:
I apologize if this is somewhat of a rookie question.

After looking online I found some articles that explain how to find the worker time per query using sys.dm_exec_query_stats. e.g.

SELECT TOP(10)
creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 AS total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime] , execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_worker_time > 0
ORDER BY total_worker_time DESC

from here.

But according to Microsoft the total_work_time only tells us the amount of time spent after the plan has been generated, not for instance, the amount of time spent building the plan for that query.

Is there a way to determine how much CPU is being spent in building the plans, and better yet, a simple breakdown of percent CPU by task (e.g. building plans, executing queries, indexing).

Thank you in advance.


texts:
I have a query like this:

SELECT HOUR(datetime) as Hour, COUNT(*) as hits, SUM(clicks)
FROM visitors_log_table
WHERE datetime >= CURDATE()
GROUP BY Hour
WITH ROLLUP

It works fine. But I don't need  GROUP BY for the clicks column.
I only need the WITH ROLLUP result for it.

This query is just an example. In my query I have many more columns like the clicks column where I don't need a GROUP BY.

My table is very, very large, so performance is important. The datetime column is indexed.
So should I do a seperate query, or should I leave it like that, or...?


texts:
If an AWS EC2 Server is connecting to an AWS RDS Database, but the database + server are not part of a VPC, and there is no SSL setup on the database...
Does that mean the data is being sent over HTTP unsecured?
My understanding is that to protect your connection to your database, you need SSL to encrypt your connection to the database, OR need to have both components in a VPC so they are isolated from public traffic. No VPC, no SSL cert on your DB connection appears to be a unsafe configuration.


texts:
I have a MDX calculated measure for rolling 30-day totals, and I want to mask the calculation for future members. I'm trying to use a CASE statement and the comparison does not work correctly. If the key of the Fiscal Month member is a datetime, shouldn't this work?

CASE
WHEN [Time].[Fiscal Month].CurrentMember < NOW()
THEN 1
ELSE 0
END


texts: Can mongorestore replay oplog via mongos?

texts:
I'm using MongoDB 3.6.2 and configured to sharded cluster.
I used mongodump to dump oplog from every shard and then restored by mongorestore via mongos, but I got below error.

  Failed: no oplog file to replay; make sure you run mongodump with --oplog

And then I restored by mongod that works.

Is it possible to restore oplog via mongos? If yes, how to do?


texts:
We are running sql server 2016 enterprise edition.
We have a linked server configured to db2.
We do a distributed transaction between mssql 2016 and db2 using MS DTC. SQL Server controls the distributed transaction.
Application is running in Java using JDBC. We are using MS JDBC driver. Java does nothing in terms of distributed transaction. From Java's perspective, it is only a local transaction with MSSQL.

Everything works fine and has been running 2+ years.

Transactions are started and committed, or rolled back on error on both mssql and db2

However we now experience a weird problem. If we run into a duplicate key (or any other error from an SQL statement), an error is thrown in the form of a SQLException (java exception). When this occurs, it seems the transaction in progress is paused and a new transaction is started. The new transaction commits, however everything that happened before in the paused transaction does not commit (nor rollback). It just hangs.

When this happens enough times, the tables lock, and we can neither read nor write from them.

There is nothing done in the application that will start a new transaction at exception.

The reason for why we have not had this issue in 2+ years is because this is the first time we expect an error to happen (duplicate key) and we therefore do some logic and continue. Previously we would always rollback immediately on exception.

I'm not sure how to troubleshoot this. Is this a documented behavior ? Where can I see the "transaction id" so I can confirm the above ? How do I proceed ?

--- Update ---

xact_abort is on and therefore the duplicate key error rollsback the transaction (but the rollback is not complete, somehow the transaction is left hanging). This explains why the commit works for the work performed after the error.
Also this is relevant: https://stackoverflow.com/questions/917773/do-i-really-need-to-use-set-xact-abort-on#18829211

The question now changes to, how to deal with statements that may throw an error but should not roll back an exception


texts: Postgresql: Insert trigger function fails on partial insert statement

texts:
We have some big tables in our database and the boss thought that partitioning would be a good improvement to our system. I was charged to investigate it and develop all the needed scripts in a test db and explain everything to the rest later. I'm no dba, I'm a perl developer.

Focused on just one master table, I've produced a function to create a new children table, to create children tables for the existing information and to move things from master to children by batches. I don't think it's relevant, but our checks are on the year and month of the created field.

Now I'm building the insert trigger in the master database that would detect the period for the new record, look for the corresponding children table, creates it if missing, and inserts. Everything is ready but the insert is not working!

Our inserts statements are usually partial: will add the basic fields, do some things and fill in the rest (the fill in part will come later). So the insert statement is something like

INSERT INTO public.transactions ( created, tr_status )
VALUES (now(), 'created');

and leave Postgres to fill in the id. But in between created and tr_status, the schema has a field called modified. And, when the data gets to the trigger function, this field is empty. Trying to insert the row will throw an error:

EXECUTE (
    'INSERT INTO ' || child || ' VALUES ' || NEW.*
);

LINE 1: ...15_07 VALUES (123456,"2015-07-03 16:48:17.890627",,completed...
                                                             ^

How can I use in the trigger the same fields that were in the insert statement?


texts: How do I find the transactions making my log grow?

texts:
I have a database with a log that is constantly growing. It is in full recovery mode, and has a log backup every 15 minutes. It is also part of an availability group, and has a readable secondary. It is currently over 22GB in size, where the data is only 15GB. I have a certain amount of time to look into this, because the drive for logs is 60GB. sys.databases.log_reuse_wait_desc is AVAILABILITY_REPLICA for this db.

I started by trying to find out why I couldn't shrink the log, and the answer seems to be because of the readable secondary in the availability group. However, "don't shrink the log - stop it growing" seemed to be a theme in the answers I looked at. So I looked for some information on that, and found this and this.

I plan to talk to our development team, and discuss their code causing the issues, but I don't want to approach them without some additional information. What can I do to find the queries that are likely to be the culprits for growing the logs?


texts: MySql Set Column on First Appearance of Value

texts:
I have a select that returns something that looks like the following:

PeopleId
Timestamp
FavoriteNumber

1
2020-11-04 17:40:33
14

2
2020-12-06 21:19:41
59

3
2020-12-10 18:46:11
14

4
2020-12-15 00:01:53
7

Given this information, I want to, for every row, check if the person's favorite number is the first appearance of that number and if so return 0, if not, return  That would make a table that looks like:

PeopleId
Timestamp
FavoriteNumber
HasOriginalNumber

1
2020-11-04 17:40:33
14
1

2
2020-12-06 21:19:41
59
1

3
2020-12-10 18:46:11
14
0

4
2020-12-15 00:01:53
7
1

Assume the query up to this point has been:
Select p.PeopleId, p.Timestamp, p.FavoriteNumber From people p

The second table could be achieved by doing:
Select p.PeopleId, p.Timestamp, p.FavoriteNumber,
    ((Select Min(x.Timestamp) from people x where x.FavoriteNumber = p.FavoriteNumber) = p.TimeStamp) as
    HasOriginalColor)
    From people p

However, I think that query will be very inefficient for large tables and seems like overkill. Is there an easier and faster way to do this?


texts:
I just found the following function in two RDS PostgreSQL 9.3 DBs:

--
-- Name: exec(text); Type: FUNCTION; Schema: public; Owner: adam
--

CREATE FUNCTION exec(text) RETURNS text
    LANGUAGE plpgsql
    AS $_$ BEGIN EXECUTE $1; RETURN $1; END; $_$;

ALTER FUNCTION public.exec(text) OWNER TO adam;

I am pretty sure I did not write this function, and since exec seems like a security hazard (does it simply execute commands?) It would like to know what is it all about. Searching the method signature didn't help me.

What does this function do?
Is it a security risk of some sort?
Which program/framework might have created it?


texts: mysqltuner: "MySQL started within last 24 hours - recommendations may be inaccurate." Why?

texts:
I'm new to db management and db tweaking. I'm using mysqltuner to get recommendations for how to improve db performance.

Mysql issues a warning message along with its recommendations:
"MySQL started within last 24 hours - recommendations may be inaccurate."
It is true that I started MySQL within the last 24 hours, but why does this matter? What does the database do over a 24 hour period that will change the results of mysqltuner?


texts: Can I lookup my Primary Key in SQL Server from Excel Spreadsheet?

texts:
Disclaimer:  I'm a database novice teaching myself for a research project.

Background:  I am querying an electronic medical record (EMR) system for information pertinent to my project and I do not want to store medical record numbers (just say "no" HIPAA violations!). The database is designed with Visit_ID as a Primary Key and four fields which result in a unique combination: admission date & time, length of stay, admitting physician, and room number. All other information will be based on the Visit_ID. For example, the "Medications" table starts with Visit_ID as a Foreign Key and all other information such as start date, stop date, etc.

Problem:  The EMR system obviously does not know my Visit_ID. Therefore, all of my queries will have the four fields which are defined by Visit_ID in my database.

Question:  Can I resolve or lookup the Visit_ID within the database from the four columns in my Excel sheet?

Example:  I query the EMR for the medications the patients had in June. My query returns an Excel spreadsheet with my four fields (Admission Date & Time, Admitting Physician, Length of Stay, and Room) and all the necessary information about the medications. Can I import data from that Excel spreadsheet into my "Medications" table but have SQL Server resolve Visit_ID instead of keeping those four fields?


texts: Oracle counterpart to `pg_cancel_backend`

texts:
Considering the Oracle statement ALTER SYSTEM KILL SESSION ... as... to some degree comparable to the PostgreSQL statement SELECT pg_terminate_backend():

Do you know of any Oracle analogy (better than ALTER SYSTEM KILL SESSION) for the PostgreSQL SELECT pg_cancel_backend()?

Reference: 9.26. System Administration Functions (PostgreSQL Documentation)

pg_terminate_backend

  Terminate a backend. You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser.

pg_cancel_backend

  Cancel a backend's current query. You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser.


texts: Databses design, parallel many-to-many relationships. Two tables, or extra fields?

texts:
Say I have want to create a database for expenses.

I will have a table for holding Person details, and a table for holding Expense details.

Then there will be two many-to-many relationships between the Person and the Expenses tables. Person Can pay an expense, and a Person can use an expense.

What is considered better? Having one relationship table to hold both "paid for" / "used by" fields. Or two have two separate tables containing each of these fields?

Two parallel tables seems more correct, but I could also see it making the SQL more complex. Is there any compelling reason to use one design over the other?


texts:
I am inexperienced with Cassandra, but I have some experience with SQL-based relational databases.

I have been unable to find best practices information about how to maintain Cassandra once deployed.  Is it necessary to VACUUM the database?  I should think that read/write loads cause fragmentation in the storage.

Or more generally: what are the best practices for maintaining a Cassandra production deployment?  What has to be done at regular intervals to maintain the health of the system?  The Operations manual really doesn't discuss this aspect.

Thanks.


texts:
I have following query with explain:

mysql> explain
     select dd.data from dane dd
       join test1.tag1 t1 on dd.id = t1.id
       join test1.tag2 t2 on dd.id = t2.id
       join test1.tag3 t3 on dd.id = t3.id
       join test1.tag4 t4 on dd.id = t4.id
       join test1.tag5 t5 on dd.id = t5.id
       join test1.tag6 t6 on dd.id = t6.id
       join test1.tag7 t7 on dd.id = t7.id
       join test1.tag8 t8 on dd.id = t8.id
       join test1.tag9 t9 on dd.id = t9.id
       join test1.tag10 t10 on dd.id = t10.id
     where t1.val = 1
       and t2.val = 2
       and t3.val = 3
       and t4.val = 4
       and t5.val = 5
       and t6.val = 6
       and t7.val = 7
       and t8.val = 8
       and t9.val = 9
       and t10.val = 10 ;
+--+-----------+-----+------+-------------+-------+-------+------------------+-----+------------------------+
|id|select_type|table|type  |possible_keys|key    |key_len|ref               |rows |Extra                   |
+--+-----------+-----+------+-------------+-------+-------+------------------+-----+------------------------+
| 1|SIMPLE     |t10  |index |PRIMARY      |PRIMARY|8      |NULL              |94577|Using where; Using index|
| 1|SIMPLE     |dd   |eq_ref|PRIMARY      |PRIMARY|4      |test1.t10.id      |    1|                        |
| 1|SIMPLE     |t8   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t10.id,const|    1|Using index             |
| 1|SIMPLE     |t1   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t8.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t2   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t1.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t6   |eq_ref|PRIMARY      |PRIMARY|8      |test1.dd.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t4   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t8.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t3   |eq_ref|PRIMARY      |PRIMARY|8      |test1.dd.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t7   |eq_ref|PRIMARY      |PRIMARY|8      |test1.dd.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t9   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t10.id,const|    1|Using index             |
| 1|SIMPLE     |t5   |eq_ref|PRIMARY      |PRIMARY|8      |test1.dd.id,const |    1|Using where; Using index|
+--+-----------+-----+------+-------------+-------+-------+------------------+-----+------------------------+
11 rows in set (0.08 sec)

Every tag1 - tag10 tables have the same build: two columns (id,val), id is the primary key, no other indexes.

I have questions about the first line of explain:

Number of rows - are this number exact number of scanned rows or just estimate? Every table has 100,000 rows.
The first row has type index, but Extra shows Using where; Using index. Does it mean, that the first full table scan on where is used, and then index? Where is on the val column which does not have any indexes.


texts: SSIS: Move data between servers - will data travel through my workstation?

texts:
I have just installed the Microsoft SQL Server Data Tools for Visual Studio 2017 on my workstation and created a simple SSIS package (.dtsx) which basically copies data from one MSSQL server to another.

Now I am wondering if the data will travel through my workstation or if there is direct connection established between the two servers. Thanks in advance!


texts: How should I back-up an application database during a release?

texts:
During an application release it is common that changes are made to the associated database schema and potentially the data it contains.  Should something go wrong with the application release, the changes made to the schema (and data) will need to be rolled back.

To throw a spanner in the works, there are multiple applications with their own schemas running on the same database instance.

What is the best way to create a back-up that would allow the application schema to be rolled back leaving all other schemas unaffected?


texts: Prevent mysql process being killed by OOM killer

texts:
I need to protect mysql process so that it won't be killed when system runs out of memory, we have a bunch of adhoc job run on the server from time to time, so I would prefer the adhoc jobs being killed rather than mysql.

I know I can adjust the oom_score_adj, by setting it to -1000, I can exclude it from OOM killer's consideration. But I am not sure if I need to restart the mysql process for new oom_score_adj to take effect? Does adjust the oom_score_adj for a running process still takes effect?

I was following this post and nobody else mentioned about restarting the server for new oom_score_adj to take effect:
https://blog.rimuhosting.com/2015/12/11/dont-let-the-oom-killer-stop-mysql/

Another dumb question is that I can't seem to write to the oom_score_adj file, always getting the permission denied. The oom_score_adj is not owned by root, I have tried a couple of options but keep getting the permission issue:

 echo "echo '-100' > /proc/5653/oom_score_adj" | sudo -u {file_owner} sh

Appreciated for any help!


texts: MySQL - Optimizing Multiple WHERE Conditions

texts:
I have to run the following 2 condition query against a table:

SELECT * FROM serial_numbers
    WHERE type = 'book' AND
    serial NOT REGEXP 'ISBN(?:-13)?:?\x20*(?=.{17}$)97(?:8|9)([ -])\d{1,5}\1\d{1,7}\1\d{1,6}\1\d$';

I have hundreds of thousands of records and this takes a long time, but I only have 100 rows that are of type 'book'. If I do something like:

 SELECT * FROM
     (SELECT * FROM serial_numbers
      WHERE type = 'book') sq
 WHERE sq.serial NOT REGEXP 'ISBN(?:-13)?:?\x20*(?=.{17}$)97(?:8|9)([ -])\d{1,5}\1\d{1,7}\1\d{1,6}\1\d$';

The 2nd query works extremely quickly. Is there a way to make it evaluate the first WHERE condition and then the 2nd without writing a subquery like this or is this the most efficient/eloquent way to write this?

Thanks.


texts: Open Management Studio with configured connections

texts: Maybe this question is a little off topic, I will close if necessary, but, is there a way to configure Management studio, to when I open it, it starts already with x connections? So I don't need to start it and connect to x instances every time.


texts:
I just installed Oracle Database Express Edition 11g Release 2.This is the first time I am using Oracle. Earlier I have used MySql and MSSql and I bet this one is a lot different !

These are the things I see in the start menu :

To start the oracle database, I click start database. Then in the prompt I do :

C:\Windows\system32>SQLPLUS / AS SYSDBA

SQL*Plus: Release 11.2.0.2.0 Production on Sat Mar 2 20:37:14 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> STARTUP
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL>

I did exactly the same as written in DOCS but then why does I see that message as I run the command STARTUP.

Also certain commands like :

show databases;
use database db_name;

don't work in it ! Why is that ?

And isn't there a graphical query browser that comes packed with this RDMS ?


texts:
I am trying to put on a scale redundant attributes and performance gain. Although storing redundant information causes many anomalies, I am solely focusing on the size of a redundant attribute vs. storing a pointer (or reference) to another tuple in different table.

So what is the size of pointer to a tuple in InnoDB Mysql systems? I found that MyISAM uses 6 bytes. Is it the same for InnoDB? For reference, 6 bytes makes storing 3 SMALLINTs (2 bytes each) in a separate table pointless in terms of storage. Perhaps an equivalent question would be: what is the size of a record address?


texts: Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?

texts:
There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for instance, I can do this..

WITH w AS (
  SELECT *
  FROM ( VALUES (1) ) AS t(x)
)
SELECT *
INTO foo
FROM w;

But, I can't do this..

WITH w AS (
  SELECT *
  FROM ( VALUES (1) ) AS t(x)
)
CREATE TABLE foo AS
SELECT * FROM w;

Or, I get

ERROR:  syntax error at or near "CREATE"
LINE 5: CREATE TABLE foo AS

How would I do that using the standardized CTAS syntax.


texts: Inner Join until null on self-referencing tables

texts:
I have the following SQL Table:

 OrganizationalUnit
    ID - PK
    ParentOrganizationalUnit - FK
    Title
    Department_Code (optional)
    Division_Code (optional)

 Person
    ID - PK
    OrganizationalUnitID - FK
    FirstName
    LastName

And for the sake of this example, these tables appear as follows:

OrganizationalUnit:
0 | (null) | Information Technology |  101   | (null)
1 | 0      | Database Management    | (null) | 4000
2 | (null) | Human Resources        |  102   | (null)
3 | 2      | Recruitment            | (null) | 4000
4 | (null) | Finance                | (null) | (null)
5 | 1      | SQL                    | (null) | (null)

People
0 | 0 | John    | Doe
1 | 0 | Jane    | Doe
2 | 1 | Josh    | Doe
3 | 1 | Jack    | Doe
4 | 3 | Other   | Doe
5 | 4 | Finance | Doe
6 | 5 | Odd     | Doe

I need to run a query so that I obtain the following output:

Output
PersonID | OrganizationalUnitID | Department_Code | Division_Code | First Name | last Name
0        |         0            |       101       |       (null)  |  John      |  Doe
1        |         0            |       101       |       (null)  |  Jane      |  Doe
2        |         1            |       101       |       4000    |  Josh      |  Doe
3        |         1            |       101       |       4000    |  Josh      |  Doe
4        |         3            |       102       |       4000    |  Other     |  Doe
5        |         4            |      (null)     |       (null)  |  Finance   |  Doe
6        |         5            |      101        |       4000    |   Odd       |  Doe

The department_code and division_code fields are used for association of OrganizationalUnits with another database. This other database is only capable of tracking up to 2 levels of hierarchy (department and division) whereas, I have been asked to track an infinite level of hierarchy, but to map the first level to departments and the second level to divisions. I have no idea where to start to write a query that could do this. I may have to redesign my ERD.


texts: Bind web service/Report Manager based on IP address AND hostname

texts:
I'm trying to get Apache installed on our main database/application server, and I'd like to give it its own IP address on a secondary NIC so I don't have to use a nonstandard port. This requires that I ensure everything else listening on port 80 is bound specifically to the primary IP address, rather than "all". This is no problem for the IIS sites, and I've already configured them appropriately.

The Reporting Services Configuration Manager seems to only give you the option of binding to a hostname or IP address, but not both. Is there a way around this via command line tools or direct editing of the config data, or will I need to add a third IP address and dedicate it to Reporting Services?


texts: RDS Oracle SE - Performance troubleshooting options

texts:
I have an RDS Oracle SE2 instance with Amazon RDS. Our team is looking forward to move couple of applications to RDS. But we’re looking for options that are available in RDS Oracle SE to perform Performance tuning and management.

As of now I’m aware I can use Statspack. With Statspack not being supported by Oracle now, I’m looking for other options compatible with AWS RDS. Will OUTLINES work with RDS for Plan management ?

Has anyone tried any other tools that could help? Please advise.


texts: SQL Server 2012 replication permissions with virtual user and agent credentials

texts:
For development/testing I set up replication in SQL Server 2012 RC0 (11.0.1750) using the default permissions on the agent, and I'm getting this error:

  Message: Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\ReplData\unc\WKSTATION$SQL2012_DB7_DB7\20120104171149\' is denied.

I'm having trouble figuring out what MS wants me to do in this case since I want to use the agent credentials. How do I give permissions to that folder if I can't see the virtual user (Nt Service\SQLAgent$SQL2012) in my local machine's user list?


texts: Using Cursor to loop through a Table variable in SQL Server

texts:
I have a parameter in the Stored Prod which gets some data in the format

1/1/2018-2/1/2018,2/1/2018-3/1/2018,3/1/2018-4/1/2018,4/1/2018-5/1/2018,5/1/2018-6/1/2018,6/1/2018-7/1/2018,7/1/2018-8/1/2018,8/1/2018-9/1/2018,9/1/2018-10/1/2018,
10/1/2018-11/1/2018,11/1/2018-12/1/2018,12/1/2018-12/31/2018

I have a function which splits the data based on , character and stores it in a table variable as shown below

declare @SPlitDates table(ItemNumber int,Item nvarchar(max))
insert into @SPlitDates
select * from dbo.SPlitFunction(@RequestData, ',')

After this I have to perform certain operations on the data range so I use cursors to loop through the temp table as shown below

DECLARE cur CURSOR FOR SELECT Item FROM @SPlitDates order by ItemNumber
OPEN cur
FETCH NEXT FROM cur INTO @monthStart
WHILE @@FETCH_STATUS = 0 BEGIN
--Some operation
End

The max data points that I will get in the temp table is the date range for 12 months.

My question is that could I be using something else apart from Cursors to improve performance or it doesn't matter when the dataset is really this small.

Thanks


texts: How to name tables which are responsible for storing dictionaries and configuration?

texts:
Below I attached diagram which corresponds to part of my database :

My questions:

In your opininon Do I have to divide ServiceInsance and ConfServiceInstance ? I did it like that because I would like to have separated IDs and Configuration columns.
What do you think about name of these tables ? I mean Is it worth to have Dict and Conf in table names ?
Is it a problem that PK does not corespond to table name by convention: TableName + ID in Conf and Dict tables ?
Is it a better idea to create schemas conf and dict maybe ?


texts: How to rebuild entire MySQL data after server re install?

texts:
I have a MySQL server running with multiple databases in a 32 bit environment. I need to rebuild my system to a 64 bit environment.

Is it possible to replicate the entire MySQL databases, users, privileges, settings, etc. after the rebuild?


texts: Are auto incrementing identities always given in the order of inserts in SQL Server?

texts:
If I create a simple table like this:

CREATE TABLE SimpleTable (
     [Id] INT IDENTITY(1,1) NOT NULL,
     [Value] VARCHAR(10) NOT NULL,
     CONSTRAINT [PK_Email] PRIMARY KEY CLUSTERED
     (
         [Id] ASC
     ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And then I insert into the table 100 times, within the same transaction, like this...

TRUNCATE TABLE SimpleTable -- ensure reset identity seed

BEGIN TRAN

INSERT INTO SimpleTable(Value) VALUES('First')
INSERT INTO SimpleTable(Value) VALUES('Second')
INSERT INTO SimpleTable(Value) VALUES('Third')
INSERT INTO SimpleTable(Value) VALUES('Fourth')
/* ... etc */
INSERT INTO SimpleTable(Value) VALUES('Hundredth')

COMMIT TRAN

Is there any guarantee that the inserted rows will be given identity values in the order in which the insert statements are executed?

More specifically, if I run this query after the inserts...

SELECT * FROM SimpleTable ORDER BY 1

Will I always see this...

Id    Value
----------------------------------------------
1     First
2     Second
3     Third
4     Fourth
... etc
100   Hundredth

Or, does SQL not guarantee this?


texts: How to get columns from system tables or views like sys.database_files (SQL Server 2012)

texts:
How can I write a query to return all columns of, for example, the sys.database_files table? I see many suggestions for using information_schema.columns for getting table names for user databases/tables, but i can't figure out how to do this for system tables.

I see that the sys.database_files is a view under the master table. I'm using SQL Server 2012.


texts: How to merge two large mysql tables into one with similar data keeping the latest record

texts:
I have two similar large tables with below table structure

CREATE TABLE tableA (
  col1 varchar(20) NOT NULL,
  col2 varchar(40) NOT NULL,
  date date NOT NULL,
  time time NOT NULL,
  PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE tableB (
  col1 varchar(20) NOT NULL,
  col2 varchar(40) NOT NULL,
  date date NOT NULL,
  time time NOT NULL,
  PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to create one large table merging above two tables with the latest record

CREATE TABLE combine_table (
  col1 varchar(20) NOT NULL,
  col2 varchar(40) NOT NULL,
  date date NOT NULL,
  time time NOT NULL,
  PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Some data may duplicated in table A and B.
How can I achieve this.
Note that these tables are very large


texts: Get INSERT/UPDATE/DELETE statements executed at specified time

texts:
We are investigating some issues with our production database where some suspicious records inserted/updated in our database. I just wanna know how we will be able to get these TSQL statements executed at a specified time. We are SQL Server 2014 with a FULL recovery option.

Sorry for my ignorance (I am not a DBA and we don't have any).


texts:
I have 2 columns col1 and col2

INITIAL QUESTION:
is it possible to make a constraint to not be able to add the same email two times in this columns, only using indexes?

The Question has been edited to:
"Is it possible to define a constraint so as to be unable to add the same email twice in these columns for the same record, using only indexes?"

But it is my fault, I did not give enough details.
When I have a unique column. I can not repeat a entry in this column.
What I need is to expand this "unique" to a second column, so that any entry made in the first column, should not be possible entered to the second column at all.

I can do it using a external program by consequently adding the entries for this two columns in a array and always checking the array before entering something to this two columns. But the solution a joke, it is sub-optimal, because by extrapolation we do not need databases any more we can use arrays.

Is this possible using just mysql, or do I need "my array" ?


texts: How to select all columns plus one alias column in MySQL

texts:
It is a simple question, but i could not find the answer.

I want to select all the columns in my table with a SELECT * statement, but i also need to alias a UUID column this way: BIN_TO_UUID(ID) as ID.

I need something like this:

SELECT BIN_TO_UUID(ID) AS ID, * FROM TABLE_NAME

Also would be nice to remove the original column that was aliased, to do not have the same column twice with different names, but just if this do not add more text to the query, because i will have to repeat this for all my 16 tables.


texts: SQL Server 2012 Index Reorganize Logfile Growth

texts:
My SQL 2012 SP3 Server is configured to run an Index Reorg job, via a maintenance plan, each weekend(not my call, it's policy, I promise!).
I have a 30GB database(not including logfile) where the transaction logfile grows to alllllmost 30GB after index reorg's.
There are several other databases in this instance that are of similar size but the logfiles are tiny, ranging from 17MB to 2.5GB.
I have read through numerous forums and articles trying to determine why this particular logfile would grow so large. Alas, I have failed, which is why I'm now asking for enlightenment.

Here are some of the configurations:

Note, most of the configurations across the databases are the same(based on sys.databases).

Recovery Model = Full (on all databases)
is_FullText_Enabled = 1 (many, not all, of the databases have this enabled), there does seem to be some correlation here, the logfiles appear to be larger where is_FullText_Enabled = 1.
No replication/transaction log shipping of any kind in this instance.
Log backups run every 3 hours, which is part of the growth problem but not allowed to change schedule/frequency.

Here are some things I've tried:

Shrunk the log to 10MB to get the VLFs down, then grew it out to 24G.
Manually ran the index reorg a handful of times to see if fragmentation would go down and eventually stop growing the logfile but that did not help.
I even rebuilt the indexes(online via maint plan) and attempted a subsequent index reorg but the log grew back out again.
Shrank the logfile between reorg/rebuild attempts. Yes, shrink = bad, I know, bad DBA, bad!

So yeah. Index maintenance is logged, I get that, but why is there a large difference in logfile growths between databases? What am I missing? What could cause this logfile to grow so much larger than the rest?

I'm a newbie, make me more smarterer please and thank you. :)


texts: I want to change to Dynamic SQL Query instead of CASE statement

texts:
I have two tables as below and description is given in below

Employee table

id EmployeeID   departmentid department1   department2 department3
1    300            100           IT          Finance
2    400            101           IT          Sales
3    500            102           Technology  Finance
4    600            101           IT                       HWD

Department table

id  EmployeeID departmentkeys Departmnetvalues
100     300      1                      IT
101     400      1                      IT
102     500      2                      Finance
103     600      1                      IT

In department table, i have departmentkeys column that contains the values 1,1,2,1 and maps to column department1,department2,department3 in the Employee table .

1 means department1
2 means department2
3 means department3

I want to fetch record based on matching records from employee and department
table.

select * from Employee e
inner join department d on e.departmentid=d.id
and Case
  When d.departmentkeys=1 then e.department1
  When d.departmentkeys=2 then e.department2
  When d.departmentkeys=3 then e.department3
End=d.departmentvalues

I do not want to to use CASE statement because its causing a performance issue. Instead of CASE statement i want to use SQL Dynamic query .


texts: Oracle 11gR2 DBCA installation failing with TNS Lost contact

texts:
I am getting below errors while running DBCA with silent install using response file

ORA-12547: TNS:lost contact

PRCR-1079 : Failed to start resource ora.ocl.db

It's a fresh installation and not upgrade.

Oracle grid stack is up and running and netca worked properly too.

I have tried below tricks/hacks/ways for this
1. Uninstalled new version of libaio and libaio-devel x86_64 and i386 packages
and Installed the required version. Then executed relink all using oracle user
2. Listeners are up and running on both nodes.
3. permission of $ORACLE_HOME/bin/oracle is correct. Sticky bit is set.
4. Every time I re-run DBCA, I remove entry from /etc/oratab. Is that sufficient.
5. To remove SID, I am not finding $ORACLE_HOME/bin/oradim, rather oradism is present.


texts: PL/SQL - Member Function: Link directly to object without parameters

texts:
I want a table generated from a table function in oracle.

Like this:

+--------------------------------------------------------------------+
| CUSTOMERS                                                          |
+----+-------+------------+-------------+---------------+------------+
| ID | FIRST |    LAST    | LOCATION_ID | CREATION_DATE | FULL_YEARS |
+----+-------+------------+-------------+---------------+------------+
|  1 | Max   | Mustermann |          54 | 2017-10-05    |          0 |
|  2 | Hans  | Dimitry    |          87 | 2016-12-26    |          1 |
|  3 | Olga  | Trausch    |          71 | 2015-06-02    |          3 |
+----+-------+------------+-------------+---------------+------------+

But I want that the column FULL_YEARS is a member function of my object customer.

So I implement my customer object as

CREATE TYPE "CUSTOMER" AS OBJECT (
    customer_id           NUMBER,
    customer_firstname    VARCHAR2,
    customer_lastname     VARCHAR2,
    customer_location_id  NUMBER,
    creation_date         DATE,
    MEMBER FUNCTION "FULL_YEARS"(in_date DATE) RETURN NUMBER
);
/

My customer object body

CREATE OR REPLACE
TYPE BODY CUSTOMER AS

  MEMBER FUNCTION "FULL_YEARS"(in_date DATE) RETURN NUMBER AS
    v_years NUMBER;
  BEGIN
    SELECT FLOOR(MONTHS_BETWEEN(SYSDATE, in_date)/12) INTO v_years
    FROM dual;
    RETURN v_years;
  END "FULL_YEARS";
END;
/

As example I declared an anonymous plsql block for testing.

DECLARE
  v_customer CUSTOMER;
BEGIN
  v_customer := CUSTOMER(2001, 'Hugo', 'McKinnock', 16, SYSDATE - INTERVAL '18' YEAR);
  DBMS_OUTPUT.PUT_LINE(v_customer.FULL_YEARS(v_customer.CREATION_DATE));
END;
/

But, how can I directly access in my member function of the column creation_date from my customer object. I don't want the parameter in_date of my member function.

I want only a call like this for example

 DBMS_OUTPUT.PUT_LINE(v_customer.FULL_YEARS());

Volker


texts:
Executing the query from here to pull the deadlock events out of the default extended events session

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
    WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

takes about 20 minutes to complete on my machine. The stats reported are

Table 'Worktable'. Scan count 0, logical reads 68121, physical reads 0, read-ahead reads 0,
         lob logical reads 25674576, lob physical reads 0, lob read-ahead reads 4332386.

 SQL Server Execution Times:
   CPU time = 1241269 ms,  elapsed time = 1244082 ms.

Slow Plan XML

If I remove the WHERE clause it completes in less than a second returning 3,782 rows.

Similarly if I add OPTION (MAXDOP 1) to the original query that speeds things up too with the stats now showing massively fewer lob reads.

Table 'Worktable'. Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0,
                lob logical reads 6767, lob physical reads 0, lob read-ahead reads 6076.

 SQL Server Execution Times:
   CPU time = 639 ms,  elapsed time = 693 ms.

Faster Plan XML

So my question is

  Can anyone explain what's going on? Why is the original plan so
  catastrophically worse and is there any reliable way of avoiding the
  problem?

Addition:

I've also found that changing the query to INNER HASH JOIN improves things to some extent (but it still takes > 3 mins) as the DMV results are so small I doubt that the Join type itself is responsible though and presume something else must have changed. Stats for that

Table 'Worktable'. Scan count 0, logical reads 30294, physical reads 0, read-ahead reads 0,
          lob logical reads 10741863, lob physical reads 0, lob read-ahead reads 4361042.

 SQL Server Execution Times:
   CPU time = 200914 ms,  elapsed time = 203614 ms.

(And plan)

After filling up the extended events ring buffer (DATALENGTH of the XML was 4,880,045 bytes and it contained 1,448 events.) and testing a cut down version of the original query with and without the MAXDOP hint.

SELECT COUNT(*)
FROM   (SELECT CAST (target_data AS XML) AS TargetData
        FROM   sys.dm_xe_session_targets st
               JOIN sys.dm_xe_sessions s
                 ON s.address = st.event_session_address
        WHERE  [name] = 'system_health') AS Data
       CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE  XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

SELECT*
FROM   sys.dm_db_task_space_usage
WHERE  session_id = @@SPID

Gave the following results

+-------------------------------------+------+----------+
|                                     | Fast |   Slow   |
+-------------------------------------+------+----------+
| internal_objects_alloc_page_count   |  616 |  1761272 |
| internal_objects_dealloc_page_count |  616 |  1761272 |
| elapsed time (ms)                   |  428 |   398481 |
| lob logical reads                   | 8390 | 12784196 |
+-------------------------------------+------+----------+

There is a clear difference in tempdb allocations with the faster one showing 616 pages were allocated and deallocated. This is the same amount of pages used when the XML is put into a variable too.

For the slow plan these page allocation counts are into the millions. Polling dm_db_task_space_usage whilst the query is running shows it seems to be constantly allocating and deallocating pages in tempdb with anywhere between 1,800 and 3,000 pages allocated at any one time.


texts: how to limit amount of rows in jsonb_agg & jsonb_build_object

texts:
I have this query:
SELECT jsonb_agg(jsonb_build_object('me_id', me_id)) from message

and I get the following result:

However I want to limit the amount of objects in the array to a certain number like 3.
How can I do this? Adding LIMIT 3 is not working.


texts: I am using Mysql 5.7 and 2-days back someone drop my table. I didn't enable my general log. How can i find the username who dropped the table?


texts: Windows Managed account to report subscription file share

texts:
Is it possible to used a Windows Managed account to access a file share in a Report subscription?

When creating a Windows file share report subscription, on Step 4 - Create a data-driven subscription, I must specify the Username and Password of the account which has access to the file share.  Can this be a Windows Managed Account and if so, what do I specify for the password to get the field to validate and allow me to move to the next screen?


texts:
I am new on maintaining database backups, i have problem on my scheduled transaction logs, it was working before until i encountered the error below.

Message:

The job failed.  The Job was invoked by Schedule 1012 (DATABASE01-Trans1PM.Subplan_1).  The last step to run was step 1 (Subplan_1).

The job was supposed to run at exactly 1pm daily, it is working before until i noticed that it's been 6 days since the same error occurs and the backup job was not successful.

Anyone have an idea on that kind of error message ?

Thank you in advance.

Regards


texts: How to improve simple update statement causing blocking issues, inneficient index scan in query plan

texts:
Please bear with me - I am on the development side but I've been tasked with finding the cause of several blocking issues after upgrading from sql server 2008 to 2016. The blocking is intermittent, everything runs fine for a couple weeks, then maybe once or twice a month it gets so bad the entire application is unusable. This is one of the repeat offending queries when that happens:

UPDATE campaign_log
SET date_modified = N'2019-06-18 14:28:19'
    , mail_disposition_status = '1'
    , status_date = N'06/17/2019'
    , activity_date =N'05/22/2019'
WHERE id = N'03938240-0112-437C-AF97-ECCF2EF78E77'

It seems like this https://www.brentozar.com/pastetheplan/?id=H1YyzoqJS query execution plan is telling me the index scan [SUGARCRM].[dbo].[campaign_log].[idx_mail_disposition_status] is really inefficient.

I'm not sure why Mail_disposition_status column is indexed or why this index is being chosen to run this query. It seems like maybe it should be using an index on id? There are 2,622,020 rows in the campaign_log table.

Can anyone point me in the right direction on what I should be looking at to fix this? I'm honestly pretty lost on what to even search for.

Editing to add definition of table - apologies but I don't have permissions to get a Create statement, so these are the fields. Also wanted to mention this is a 3rd party product so the table design is just stock.

id, varchar(36)
campaign_id, varchar(36)
target_tracker_key, nvarchar(36)
target_id, varchar(36)
target_type, nvarchar(100)
activity_type, nvarchar(100)
activity_date, datetime
related_id, varchar(36)
related_type, nvarchar(100)
archived, bit
hits, int
list_id, varchar(36)
deleted, bit
date_modified, datetime
more_information, nvarchar(100)
marketing_id, varchar(36)
prospect_address_id, varchar(36)
mail_disposition_status, nvarchar(50)
status_date, datetime
mail_index, int

Here is the definition for Index idx_mail_disposition_status.


texts: One generic mapping table VS many specific mapping tables

texts:
I'm trying to design database tables to track files uploaded by end users.
Files can be uploaded under different contexts.
Each context is a different table.

As an example:

An employee can upload multiple "receipt" files against a "weekly expense" entry.
An employee can upload multiple "photo" files against a "pets" entry.

The entities / tables in this (contrived) scenario are:

Employees (Id, Name)
Expenses (Id, Date, RequestedRefund, RequestedByEmployeeId)
Pets (Id, Name, Type, BelongsToEmployeeId)

I already have a table called Files which tracks information about the file itself:
Files (Id, Name, Size, Extension, Folder)

My question is around how to map a expense receipt upload  and pet photo upload against the file record. I know I can do this in 2 ways:

1st Option:

Have one generic mapping table: GenericFileMap (FileId, ContextId, Type)
where

FileId is the id of the file record
ContextId is the id of the context record that i'm trying to retrieve
Type is the field to describe the context itself

In this scenario, I'd fetch all expense receipts for an employee like so:

SELECT *
FROM Employee e
INNER JOIN Expenses ex ON e.Id = ex.RequestedByEmployeeId
INNER JOIN GenericFileMap g ON g.ContextId = ex.Id AND g.Type = 'expense'
INNER JOIN Files f ON g.FileId = f.Id

2nd Option:

Have separate mapping table for each entity / context table:

ExpenseFiles (ExpenseId, FileId)
PetFiles (PetId, FileId)

The query for receipts would be:

SELECT *
FROM Employee e
INNER JOIN Expenses ex ON e.Id = ex.RequestedByEmployeeId
INNER JOIN ExpenseFiles ef ON ef.ExpenseId = ex.Id
INNER JOIN Files f ON ef.FileId = f.Id

I believe the 1st option is not the "normalized" way to do things in a relational database. But the only reason i'm considering this is, in my particular situation, there are at least 15 different contexts from which a user can upload / attach files.

The only benefit i see from the 1st option is the time I'd save creating and duplicating tables and stored procedures (one each for CRUD) for each and every context.

However, it does seem that the 2nd option is the "right" way to do things.

My questions:

Which of the two options is the smarter approach?
Which of the two is better for performance in the long run (I'll definitely put in the extra effort needed if the second option will be more performant) ?


texts: Multiple UNIONs get slow. Indexing or materialized views?

texts:
Let me start by stating that my DBMS is PostgreSQL 9.4.

I have a database with, say 30 tables. Each table has a mirror table with historical data, that is the same columns and some timestamps.

To service a webpage, where all kinds of history is filterable and sortable, I need some way to combine these 30 tables, so that I can sort by e.g. the "changed_at" date. In this case, "changed_at" is a column that is common between all the tables. Other columns may be unique to the different tables (which would make the UNIONs a little weird, since I would have to pad with NULL columns to make the tables fit together)

My first idea is to use UNION, e.g.

table1 UNION table2 UNION ... UNION table30

There are some additional requirements, which make these 30 queries non-trivial (each need to join another table to get some values).

Ultimately, this gets pretty slow. So my question is how to improve on this?

Can I use indexing to somehow speed things up?
Are there an alternative to UNION, that I am not aware of?
Will materialized views be the solution?

Since it is the joining that is slow, I would imagine that the materialized view would be a big help here. I know there is the caveat that the materialized view does not represent current data, as it needs to be refreshed. I believe this fits okay with my requirements, depending on how often I can refresh the view (would every 30 minutes be okay?)

I know I may have essentially solved my problem, but I am here for pointers as to what I may have missed.

An concrete example that I tested (with just two unions) can be found below. Each query joins a "comments" table to attach a comment and has a lateral self join to attach the previous version for each row.

(
  SELECT 'control', a.id, a.sys_period, p.sys_period as p_sys_period, b.comment, b.author_id
  FROM controls_control_history_view as a
    -- With comments
    LEFT JOIN log_changecomment as b
      ON referenced_table = 'controls_control_history'
         AND referenced_id = a.id
         AND b.referenced_date = lower(a.sys_period),
    -- With previous
    LATERAL (
    SELECT * FROM controls_control_history_view prev
    WHERE prev.id = a.id AND
          prev.sys_period < a.sys_period
    ORDER BY prev.sys_period DESC
    LIMIT 1
    ) AS p
-- Unioned
) UNION ALL (
  SELECT 'action', a.id, a.sys_period, p.sys_period as p_sys_period, b.comment, NULL
  FROM actions_action_history_view as a
    -- With comments
    LEFT JOIN log_changecomment as b
      ON referenced_table = 'actions_action_history'
         AND referenced_id = a.id
         AND b.referenced_date = lower(a.sys_period),
    -- With previous
    LATERAL (
    SELECT * FROM actions_action_history_view prev
    WHERE prev.id = a.id AND
          prev.sys_period < a.sys_period
    ORDER BY prev.sys_period DESC
    LIMIT 1
    ) AS p
)
ORDER BY sys_period
;

The EXPLAIN ANALYSE output can be found here


texts: Good tool for making MySQL queries graphically?

texts:
After working a while with Access 2010 I have fell in love with the way you make queries. You just pick the tables you want in your SELECT query, then you get all the JOINs laid out automatically, and you can just pick and choose what you want.

However, I cannot find any good similar tools for MySQL. I tried MySQL Workbench once, but it wasn't very easy to use compared to Access. I'm seriously considering making a parallel Access database to construct the JOINs, even though it requires me to do structural changes both in Access and phpMyAdmin, and to make dummy data in Access, and to convert the SQL to MySQL-friendly code..

Are there any GOOD tools out there to help me do this instead of my Access/MySQL in parallel approach?


texts:
We have been plagued by a deadlock issue for quite some time that relates to deletes and inserts on the same table.  It happens sporadically, but had been increasing in frequency.  It would manifest itself as a DELETE FROM MYTABLE by unique ID statement BLOCKING INSERT INTO MYTABLE statements.

Initially, we could not see the bind variables because we were using Hibernate and we could not turn on show_sql for Hibernate since there is far too much traffic on other Hibernate-managed POJOs in other areas of the application.  We had thought Hibernate might be doing something with its cache at unexpected times.

Recently, I removed Hibernate for the table in question, replaced it with JDBC, and log EVERY touch (every SELECT/INSERT/UPDATE/DELETE) against that table.

After doing that, I've only seen 1 "blocker".  So I don't know if the problem is fixed by virtue of removing Hibernate and doing everything via JDBC.  However, I would be remiss if I didn't attempt to run down that blocker instance.

Here's what I see:

A DELETE FROM MYTABLE by unique ID took almost 10 seconds at one point.
There are no other "touches" on MYTABLE for that Unique ID on or around the time of the DELETE FROM MYTABLE by Unique ID.
That DELETE blocked other INSERT INTO statements.
The "blocker" Delete statement completed in 10 seconds and cleared itself, and after that, everything was fine.
It only happened once, the rest of the day DELETE FROM MYTABLE by Unique ID would complete sub-second, as one would expect.

I verified with our DBAs that:

We do not have any on delete triggers on the table in question.
All FKs in that table are indexed.

Does anyone have any insight/recommendations on what might cause a Delete statement by Unique ID to possibly take 10 seconds, when there is no other touches on that table for that row (no select, update, or insert by that Unique ID)?

I realize that there probably isn't a precise and exact answer to this questions, I'm just hoping someone can share insight or anecdotal information on their own experiences.

Thank you sincerely in advance.


texts: Check if all record columns of 2 Tables/Query are equal

texts:
In SQL Server 2005, I would like to make one query that checks if all record columns of 2 tables are the same,
Example:

declare @tbl1 table(col nvarchar(50))
declare @tbl2 table(col nvarchar(50))

insert into @tbl1
    select '11' union select '22'
insert into @tbl2
    select '22'
--should return false

declare @tbl1 table(col nvarchar(50))
declare @tbl2 table(col nvarchar(50))

insert into @tbl1
    select '11'
insert into @tbl2
    select '11' union select '22'
--should return false

declare @tbl1 table(col nvarchar(50))
declare @tbl2 table(col nvarchar(50))

insert into @tbl1
    select '11' union select '22'
insert into @tbl2
    select '11' union select '22'
--should return true

Using IN clause, or LEFT JOIN i'm only able to check if all record columns in one of the tables are present on the other.

Edit: Order is irrelevant


texts: Create an XML Schema collection in SQL Server 2012?

texts:
I want to create an XML schema collection to validate some XML in a column for the following XML:

DECLARE @xml as xml
SET @xml='

<R C="firstName" O="" N="Fred" />
    <R C="lastName" O="" N="Bloggs" />
    <R C="emailAddress" O="" N="FredBloggs@gmail.com" />
    <R C="phone" O="" N="" />
    <R C="mobile" O="" N="" />
    <R C="fax" O="" N="" />
    <R C="OrganisationId" O="" N="286" />

'

SELECT AuditDetail.Col.value('(@C)[1]','nvarchar(80)')    DatabaseColumnName,
       AuditDetail.Col.value('(@O)[1]','nvarchar(MAX)')   OldValue,
       AuditDetail.Col.value('(@N)[1]','nvarchar(MAX)')   NewValue
  FROM @xml.nodes('/R') AS [AuditDetail](Col)

The validation should be that
D is nvarchar(80)
O is nvarchar(MAX)
N is nvarchar(MAX)

Can someone show me what the schema collection script would look like?


texts: Is there a way to get a history of transaction log used space in SQL Server?

texts:
I would like to be able to look back and see what the used space was in transaction log over a given period. Not interested in growths or file size as they don't tell me how much space is actually being used.

I am switching from simple model to full recovery model and need an idea of how large the transaction log will get.


texts: SSIS failure flow error. The decision after a sequence container failure does not proceed with the correct flow

texts:
I have an SSIS sequence container that extracts data from individual source database tables into a Stage layer. However, I need to know when a given calculation crashes in order for the given error to be written to the configuration table, which is provided by the yellow highlighted task in which the procedure is called. The problem is that if a given container falls due to an error, it does not flow through failure precendence constraint into the given task (LogPackageFailed).
I tried setting FaiPackageOnFailure together with FailParentOnFailure to TRUE in the properties, but it didn't help. The proposed solution in the following links does not work either:

http://techblog.elish.net/2010/12/ssis-foreach-loop-container-continue-on.html
https://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/

, which was addressed in a previous similar query.


texts:
I am trying to Import a CSV file with a column called timestamp to MySQl to a column with the structure timestamp but all i get are NULLS in the columns is there a way that i can import the timestamp as the structure to my new table.

Transaction Timestamp,Batch Ref No,Transaction Type,Group Transaction No,Receipt No,Transaction No,Transaction Amount,Actual Cost,CErr,Resource Units,Resource Units Error,Operator Name,Account Number,Debt Reference No,Meter Serial No,Token,Comment,Station,Database Name,Resource,Tax Item Name,Fixed Charge Item Category,Debt Category,Utility Name,Vend Reason
6/1/2012 0:09,SH/EVG4/1108,Tax Transaction,EVG400021324,,EVG400050154,2761.54,0,0,0,0,EFULUSI,AGR1784471690,,1318958574,,,STN EVG4_CLIENT,TANOLS01,Electricity,VAT + EWURA + REA,,,Tanesco,
6/1/2012 0:09,SH/EVG4/1108,Fixed Cost Transaction,EVG400021324,,EVG400050155,3841,0,0,0,0,EFULUSI,AGR1784471690,,1318958574,,,STN EVG4_CLIENT,TANOLS01,Electricity,,Basic Support Charge,,Tanesco,
6/1/2012 0:09,SH/EVG4/1108,Tax Transaction,EVG400021324,,EVG400050156,845.02,0,0,0,0,EFULUSI,AGR1784471690,,1318958574,,,STN EVG4_CLIENT,TANOLS01,Electricity,VAT + EWURA + REA,Basic Support Charge,,Tanesco,

that is a sample of the CSV file that am trying to import everything else imports well except the timestamp, my structure is so that the timestamp structure is set to "timestamp"


texts: Can't see some stored procedures in sys.procedures

texts:
Documenting a database and find that I cannot see stored procedures in sys.procedures, even though I can see them in Object Explorer.

select p.name, x.value, p.modify_date
from sys.procedures p
left join sys.extended_properties x
on p.object_id = x.major_id
where p.is_ms_shipped = 0
and p.name NOT LIKE 'sp_%'