texts: How to create an index on CASE expression in Postgres

texts:
I am trying to create an index on CASE expression, as follows

CREATE TABLE test(i INT, j INT);

CREATE UNIQUE INDEX test_index ON test(CASE WHEN i=1 THEN j END);

Getting this error:

ERROR:  syntax error at or near "CASE"
LINE 1: CREATE UNIQUE INDEX test_index ON test(CASE WHEN i=1 THEN j ...
                                               ^

********** Error **********

ERROR: syntax error at or near "CASE"
SQL state: 42601
Character: 40

What am I doing wrong?

Postgres 9.5.2


texts: How to increase donor speed on mariadb galera cluster?

texts:
hi currently i have database with size 200gb
and my every node at 1Gb/s so when 1 node is down and then 2 node will be donor i config mariadb donor with wsrep_sst_method = rsync method. but it's take long time (more 3 hours).
this impact from node network specifications ?
and need scaling up the network ?


texts: How to select subset JSON with PostgresQL?

texts:
I'm trying to deal with a JSON column that does not have an explicit JSON schema or convention. There are varying keys within the JSON. Some keys do not exist, some keys do exist but empty string values.

For example:

CREATE TABLE json_table (
    id         int,
    json_data   json
);

INSERT INTO json_table (id, json_data) VALUES
  (1, '{"foo" : "bar", "baz": "biz"}');
  (2, '{"foo" : "", "baz": "biz"}');
  (3, '{"hello" : "world"}');
  (4, '{"hello" : "world2", "foo" : "bar2", "baz" : "" }');

Is it possible to query this table and select/yield a subset of json_data with only key-value pairs that are non-empty strings?
Is it possible to query this table and select records that contain JSON keys that are within a given list? (I essentially want to find records with json_data that have the key "foo")

For #2 I do have a solution that selects only records where json_data keys are within a regex pattern:

SELECT tmp.*
FROM (
    SELECT id,
           ARRAY(SELECT json_object_keys(json_data))::text AS keys
    FROM json_table
) tmp
WHERE tmp.keys LIKE ANY(ARRAY['%foo%', '%bar%']);

This yields:

"id","keys"
1,"{foo,baz}"
2,"{foo,baz}"
4,"{hello,foo,baz}"

But there's a caveat! I'm returning records that DO have the keys, but empty string values! Is there a way to incorporate a solution to #1 into this and pre-filter the JSON to only "valid" key-value pairs?


texts: How do I CASCADE DROP a table being referenced in MariaDB?

texts:
If I have a table that references another one

CREATE TABLE foo ( x int PRIMARY KEY );
CREATE TABLE bar (
  x int,
  CONSTRAINT asdf FOREIGN KEY (x) REFERENCES foo(x)
);

DROP TABLE foo;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

In PostgreSQL I can easily use DROP ... CASCADE

  CASCADE Automatically drop objects that depend on the table (such as views), and in turn all objects that depend on those objects (see Section 5.13).

Like this,

# CREATE TABLE foo ( x int PRIMARY KEY );
# CREATE TABLE bar ( x int REFERENCES foo );
# DROP TABLE foo CASCADE;
NOTICE:  drop cascades to constraint bar_x_fkey on table bar
DROP TABLE

However, in MySQL, even with CASCADE you get an error,

DROP TABLE foo CASCADE;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails


texts: Query to find optimal filegroups for objects

texts:
I have been assigned a task to create four file groups (one per volume) locate tables so that they are located in a manner that during an ETL process no filegroup is being read and written to at the same time.

I have stepped through all the packages, data flow tasks and procedures and views and noted which tables are being read from and to and in what sequence.

I now need write a query to determine the optimal positioning of the tables.

In terms of logic I have the following, broken it down into the following rules.

For each sequence source table and destination table can not be on the same volume.
Tables can not exist on more than one volume.

I am interested to hear peoples opinion on whether this process is worthwhile but regardless I would appreciate some help resolving the query.

Below you can find the scripts to create the table and insert some test data.

Thanks in advance for any suggestions.

-- Create table
CREATE TABLE [dbo].[TestData]
(
    [TestDataID] [smallint] IDENTITY(1,1) NOT NULL,
    [Src] [nvarchar](50) NOT NULL,
    [Dest] [nvarchar](50) NOT NULL,
    [Seq] [int] NOT NULL,
CONSTRAINT PK_TestData PRIMARY KEY CLUSTERED (TestDataID)
)
GO

-- Insert data
INSERT INTO TestData(Src, Dest, seq)
 SELECT 'A', 'B', 1
 UNION ALL
 SELECT 'A', 'C', 1
 UNION ALL
 SELECT 'C', 'D', 2
 UNION ALL
 SELECT 'A', 'D', 3
 UNION ALL
 SELECT 'B', 'D', 3
GO


texts: Database design to record occurences in tv show

texts:
I want to make a database where I record the guests of each episodes of a tv show. I want to be able to do the following stastics:

What guest had the most appearences?

Which two (or three) guests appeared together the most?

How many unique guests did the show have each month/year?

What are the gender distribution between the guests?

What show had the highest number of guests?

What is the average number of guests on each episode of the show?

I also collect the description of each episode, so I want to find the most common topic for the episodes, which topic has which guests etc. The topics is retrieved from keywords in the episode description. An episode has everywhere from 2 to 10 topics.

...and possible more statistics I find that I want along the way.

However, how should I model it? Since each guest can have hundreds of apparences, I don't think an relational database where I map guest to episode is smart ->

| Guest Name | | Episode appeared in |

Since the second column can be filled with values.

Note that each episode can have ten to twenty guests as well.

Is a FACT table a better design, where I have the following:

|Date/Episode number | | Guest ID |

with an own guest info table?


texts: How to track if employees are late which has more than one work schedules or shifts?

texts:
I have designed a MySQL database for an employee monitoring system, it was working perfectly well until I was asked to put a new work schedule for some of the employees.

The new schedule was what they called a broken schedule because an employee would have different shifts in one day, for example, an employee could be given a work schedule for a given day starting from 7:00AM-11:00AM and then continues at 3:00PM-7:00PM...

The problem I encountered now is, I am confused on how to keep track if the employees are late of not now that some employees have two or more work schedules...

Could anyone help me pls?

addendum:

for more clarity, lets say we have employee A. employee A for example has the following work schedule for a given day - 5 days in a week.
        7:00AM-11:00AM - shift 1
        3:00PM-7:00PM  - shift 2
        9:00PM-11:30PM - shift 3

then, lets say employee A logged in at these different scenarios:
scenario #1: logged in at 7:10AM
scenario #2: logged in at 2:30PM

how do I check that employee A's log info at scenario #1 is not referencing shift2 or shift3?
likewise, how do I check if employee A's log info at scenario #2 would only reference shift2 only and would not make him/her late at his/her schedule at shift #1 or too early at shift #3?


texts: Using an UPDATE trigger to populate Eastings and Northings in PostgreSQL

texts:
I am wondering if it is at all possible to create trigger that populates the X and Y of a created point into 2 separate fields called 'easting' and 'northing'?

It is an update trigger on a table which contains point geometries.

It has fields called 'easting' and 'northing'. These fields need to converted from integer to double precision due to this error code on saving of the table.

Provider errors:

PostGIS error while adding features: ERROR:  invalid input syntax for integer: "387733.568253859"
CONTEXT:  PL/pgSQL function "tf_xy_27700" line 5 at assignment'*

So I have converted them to double precision. I now need to get these fields to update with the x/y on update.

See below for my code so far:

CREATE OR REPLACE FUNCTION tf_xy_27700()
    RETURNS trigger AS
$BODY$
    BEGIN
        IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN
            new.easting := ST_X(new.wkb_geometry);
            RETURN NEW;
        END IF;
    END;
$BODY$
LANGUAGE plpgsql;


texts: Statment running significantly slower than component parts and causes high CPU usage

texts:
I am running the following statement in SQL Server Studio 2008:

Use Systems

select subdirectory
from [Systems].[dbo].[ScanningArchiveFileSystem] f
where f.FileFlag = 1
and SubDirectory not in
(
    Select m.strSchemeCode + N'\' + f.FileRef AS P3_Skem
    from  AdminWIP_DS.dbo.fnTblMembers(NULL) AS m
    RIGHT OUTER JOIN AdminWIP_DS.dbo.tblScannedItems f
    ON m.intMemRef = f.intMemRef
    AND m.intSystem = f.intSystem
    where RIGHT(m.strSchemeCode + N'\' + f.FileRef,5) like '%.%'

    union

    select (m.skem COLLATE Latin1_General_CI_AS  + '\' + f.strPath COLLATE latin1_General_CI_AS ) as filepath
    from AdminWIP_DS.dbo.tbl_Client_BAY_MemberFiles f
    join p3Test.dbo.MEMBERS m
    on m.MEMNO = f.intMemRef
    and RIGHT(f.strPath,3) = 'pdf'
)

Each select statement runs with the following times:

select subdirectory
from [Systems].[dbo].[ScanningArchiveFileSystem] f
where f.FileFlag = 1

Rows 247769, Time 1 Secs

Select m.strSchemeCode + N'\' + f.FileRef AS P3_Skem
from AdminWIP_DS.dbo.fnTblMembers(NULL) AS m
RIGHT OUTER JOIN AdminWIP_DS.dbo.tblScannedItems f
ON m.intMemRef = f.intMemRef
AND m.intSystem = f.intSystem
where RIGHT(m.strSchemeCode + N'\' + f.FileRef,5) like '%.%'

Rows 330442, Time 6 Secs

select (m.skem COLLATE Latin1_General_CI_AS  + '\' + f.strPath COLLATE latin1_General_CI_AS ) as filepath
from AdminWIP_DS.dbo.tbl_Client_BAY_MemberFiles f
join p3Test.dbo.MEMBERS m
on m.MEMNO = f.intMemRef
and RIGHT(f.strPath,3) = 'pdf')

Rows 63133 0 Secs

and finally the Union as a whole runs as Rows 237660, Time 14 secs.

When I bring them all together for the "Not in" where clause CPU usage on the SQL Server goes up to 100% and so far I let it run for 20 mins and it didn't complete.

I'm not a SQL whiz so I was wondering if anyone could help with why the whole statement can take significantly longer.


texts: What is the difference between bridge table and helper table in data warehouse's context?

texts:
From what I could understand :- Bridge tables are used when a dimension table can not be directly associated with a fact table.

e.g. In a bank's data warehouse a fact table of balance of a customer can't be stored with a customer ID as link between fact table and customer dimension due to the fact that multiple customers can be associated with same bank account. (i.e. joint account)

So fact table store account ID and bridge table between account dimension and customer dimension is used to distinguish.

But how are they different from helper tables which resolve many to many relationships between fact table and another dimension?


texts:
Using LEMP on popular cloud-service virtual server, with MyISAM tables.

The problem is that sometimes once in few hours simple mysql queries (select from where) executing several seconds (5-9) instead of milliseconds (0.002) like in other time.

MySQL slow.log has these records:

  ...
   Query_time: 5.369666 Lock_time: 0.000120 ...

error.log is empty and in other system logs have nothing special too

Such slow queries goes one by one to several databases and tables so its not looks like the table lock and lock_time value in slow.log is also insignificant.

I was enabling general log but there was nothing strange. Just regular queries so I cant find any correlation. I also didnt find out memory or cpu leak. Looks like sometimes something load MySQL a lot and it slowing down.

What can be the reason of such peaks of MySQL slowdown and how can find it out?


texts: Recommendations about deleting large set of rows in MSSQL

texts:
I need to delete about 75+ million rows from a table everyday that contains around 3.5 billions of record.
Database recovery mode is simple, I have writen a code that deletes 15.000 rows in a while condition until all 75M records is deleted. (i use batch delete due to log file grow) However, with current deletion speed it looks like it will take at least 5 days, which means that amount of data required to be deleted is multiply faster than my deletion speed.
Basically what i'm trying to do is summarizing (in another table) and deleting data older than 2 months. There is no update operation in that table, only insert and delete.
I have an enterprise edition of MSSQL 2017
Any suggestions will be welcome.
Table definition: (for giving you clear view)
Table has 3 indexes, clustered bigint Id index, non-clustered (Datetime + bit + int) index and non clustered columnstore index that contains every column(this required by ui application), Straight forward way looks like creating partition scheme over CreateDate column that creates partitions for every day. Then drop that partition when its data marked to be deleted. However with these approach i have encountered major performance impact, almost entire system crashed.


texts: MariaDB / MySQL client table-formatting like `psql`?

texts:
I hate to nitpick over style, but for the purposes of use Database Administrators is there a way to clean this up a bit,

SELECT 1 AS "myCol1", 2 AS "myCol2";
+--------+--------+
| myCol1 | myCol2 |
+--------+--------+
|      1 |      2 |
+--------+--------+

For comparison, with PostgreSQL,

SELECT 1 AS "myCol1", 2 AS "myCol2";
 myCol1 | myCol2
--------+--------
      1 |      2

Can I get the psql output from MySQL or MariaDB?

I know `psql, the client for PostgreSQL has a ton of options for formatting the output table

\C Sets the title of any tables being printed as the result of a query or unset any such title. This command is equivalent to \pset title title. (The name of this command derives from “caption”, as it was previously only used to set the caption in an HTML table.)
\a If the current table output format is unaligned, it is switched to aligned. If it is not unaligned, it is set to unaligned. This command is kept for backwards compatibility. See \pset for a more general solution.
\pset,

border
columns
expanded
fieldsep
fieldsep_zero
footer
format (supports html, asciidoc, latex, latex-longtable, and troff-ms)
linestyle
null
numericlocale
recordsep
recordsep_zero
tableattr
title
tuples_only
unicode_border_linestyle, unicode_column_linestyle, unicode_header_linestyle
and on, and on.


texts:
I'm designing a database for a poll system.

Questions can be one of two types: a numerical rating (1-5) or a voice record.

My idea is to have questions with a type field to specify if it's a numeric rating or an audio question.  I'd have two fields, response to store the numeric answers and audio_id to link to another table that can store the audio files.

However, in this design, there will always be a null field in each record. Is this is a good approach to solve the problem?


texts:
I have three tables right now in a postgres DB:

Artists
ID  Name

Shows
ID  Date    Venue   Price   Is_sold Ages    Time    Multi_day   Pit

Artists_shows
ID  Show_id Artist_id

A show can have one or many artists; eventually I'm going to produce JSON for all shows that looks like this:

[
    {
    "bands": [
        "Domino And The Derelicts",
        "Creeper",
        "Lesbros"
    ],
    "date": "2015-12-26T08:00:00.000Z",
    "venue": "Stork Club, Oakland",
    "time": null,
    "soldOut": false,
    "pit": false,
    "multiDay": false,
    "ages": "21+",
    "price": null
    }

...]

Right now I'm doing this pretty inefficiently.

First, I'm running a query: SELECT * FROM shows WHERE date >= ${date} ORDER BY date, where date is today's date.

Then, I'm taking the result and running a for loop through each individual show that results and running this query: SELECT artist.* FROM artist INNER JOIN artist_shows ON artist.id = artist_shows.artist_id AND artist_shows.show_id=${id}, where id is each id from the results of the first query on the Shows table.

I'm then stitching together the results of both queries to produce the resulting JSON.

Is there a way to perform a single JOIN operation on my tables so that I can avoid the loop that I'm running? I assume that there might be a way to merge my existing join with my first query so that I get a table with all of the artists and all of the show details in one table. The thing that is tripping me up is the fact that a show can have many artists associated with it.


texts: Where can I get a SQL Server Trace Dataset?

texts:
I need a huge SQL Server query trace file (like the profiler trace) with the SQL query text and the time-stamp. I need a trace of SQL traffic produced by a real application because I want to do some pattern analysis. I check many online dataset sites, but I couldn't find one. I don't think anyone will set a profiler trace in production environment since it reduces the performance. But I need a huge trace file (or just SQL Text and Time-stamp will be enough) with the database (with schema and data). Is it possible to get such a data-set ?

At least is it possible to find a data-set with 24 hours of trace?
Does Microsoft has trace files for it's sample databases such as AdventureWorks?


texts: Getting Running Total from user defined function

texts:
I have the following query.

Select x1.TotalAmount, [ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount,
       x2.DocumentType
FROM table1 X1
  INNER JOIN table2 x2 on t1.id = t2.t1id

Now how do I get the running total of self-declared TaxAmount? I wish to do something like this

Select x1.TotalAmount,
       SUM([ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount) OVER (PARTITION BY x3.InvId
          ORDER BY x3.createddate
          ROWS BETWEEN UNBOUNDED PRECEDING
          AND 1 PRECEDING) AS TaxAmount,
       x2.DocumentType
FROM table1 X1
  INNER JOIN table2 x2 on t1.id = t2.t1id

[ReturnTaxAmount] is a function that will return calculated decimal value.

This is the method I tried but failed which suspected is because of the table hasn't really being generated yet.

Is it possible to do so or must I create a new declared/temp table to store the table and select the table again to get the running total of it.


texts: Can PostgreSQL index calculations be multithreaded?

texts:
During a single query write of approximately 300,000 rows with 30 columns all indexed, I noticed that only one core was maxed at a time.

Is there any way to calculate indexes multithreaded for single inserts?


texts:
Whenever I try to start the listener (I just have only one Oracle database) I get the following:

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=admin-41f89f35d)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error

tnsnames.ora

 # TNSNAMES.ORA Network Configuration File: F:\Oracle\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    PROJECT =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = admin-xxxxxx)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = PROJECT)
        )
      )

    INST1_HTTP =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = admin-xxxxxx)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVER = SHARED)
          (SERVICE_NAME = MODOSE)
          (PRESENTATION = http://HRService)
        )
      )

    EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
        )
        (CONNECT_DATA =
          (SID = PLSExtProc)
          (PRESENTATION = RO)
        )
      )

listerner.ora

# LISTENER.ORA Network Configuration File: F:\Oracle\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = admin-xxxxxx)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = F:\Oracle)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = PROJECT)
      (ORACLE_HOME = F:\Oracle)
      (SID_NAME = PROJECT)
    )
  )

I really don't know what the problem is. My tnsnames.ora and listener.ora files looks fine to me.


texts: How to limit maximum number of rows in a table to just 1

texts:
I have a configuration table in my SQL Server database and this table should only ever have one row. To help future developers understand this I'd like to prevent more than one row of data being added. I have opted to use a trigger for this, as below...

ALTER TRIGGER OnlyOneConfigRow
    ON [dbo].[Configuration]
    INSTEAD OF INSERT
AS
BEGIN
    DECLARE @HasZeroRows BIT;
    SELECT  @HasZeroRows = CASE
        WHEN COUNT (Id) = 0 THEN 1
        ELSE 0
    END
    FROM
        [dbo].[Configuration];

    IF EXISTS(SELECT [Id] FROM inserted) AND @HasZeroRows = 0
    BEGIN
        RAISERROR ('You should not add more than one row into the config table. ', 16, 1)
    END
END

This does not throw an error but is not allowing the first row to go in.

Also is there a more effective / more self explaining way of limiting the number of rows that can be inserted into a table to just 1, than this? Am I missing any built in SQL Server feature?


texts: Can you grant an Active Directory Group rights on a linked server?

texts: I am trying to grant rights to an Active Directory Group on a Linked Server. I am unable to do it; it only allows me to grant rights to individual active directory users. Does anyone know how to achieve this?


texts: Dynamic conversion of long data to wide with multiple columns

texts:
I'm trying to convert long data:

ID|SchoolID|Section|RepScore|SportsScore|PartyScore
1 |20      |1      |23.2    |70.2       |42.3
2 |20      |6      |65.2    |75.8       |52.3
3 |20      |7      |77.2    |72.2       |66.3
4 |21      |10     |13.2    |40.2       |72.3
5 |21      |11     |25.2    |55.8       |72.3
6 |21      |12     |37.2    |62.2       |76.3

to wide data (I am using only the first three rows to keep it short here):

SchoolID|RpScr1|RpScr6|RpScr7|SprtScr1|SprtScr6|SprtScr7|Prty1|Prty6|Prty7
20      |23.2  |65.2  |77.2  |70.2    |75.8    |72.2    |42.3 |52.3 |66.3

I have tried this:

DECLARE @cols VARCHAR(1000)
DECLARE @cols2 VARCHAR(1000)
DECLARE @cols3 VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)
SELECT  @cols = STUFF(( SELECT distinct  '], [a' + cast([Section] as varchar)
                        FROM [dbo].[SchoolData] FOR XML PATH('') ), 1, 2, '') + ']'
SELECT  @cols2 = STUFF(( SELECT distinct  '], [b' + cast([Section] as varchar)
                        FROM [dbo].[SchoolData] FOR XML PATH('') ), 1, 2, '') + ']'
SELECT  @cols3 = STUFF(( SELECT distinct  '], [c' + cast([Section] as varchar)
                        FROM [dbo].[SchoolData] FOR XML PATH('') ), 1, 2, '') + ']'
SET @sqlquery = 'SELECT * FROM
      (SELECT z.Section,z.RepScore,z.SportsScore,z.PartyScore
       FROM [dbo].[SchoolData] z) base
       PIVOT (Max(RepScore) FOR [Section] IN (' + @cols + ')) AS finalpivot
       PIVOT (Max(SportsScore) FOR [Section] IN (' + @cols + ')) AS finalpivot2
       PIVOT (Max(PartyScore) FOR [Section] IN (' + @cols + ')) AS finalpivot3'

EXECUTE ( @sqlquery )

  This is the error I get:   Msg 8114, Level 16, State 1, Line 7 Error
  converting data type nvarchar to int. Msg 473, Level 16, State 1, Line
  7 The incorrect value "a9" is supplied in the PIVOT operator. Msg 207,
  Level 16, State 1, Line 8 Invalid column name 'Section'.

When I try it with just one like this:

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)
    SELECT  @cols = STUFF(( SELECT distinct  '], [a' + cast([Section] as varchar)
                            FROM [dbo].[SchoolData] FOR XML PATH('') ), 1, 2, '') + ']'
SET @sqlquery = 'SELECT * FROM
          (SELECT z.Section,z.RepScore,z.SportsScore,z.PartyScore
           FROM [dbo].[SchoolData] z) base
           PIVOT (Max(RepScore) FOR [Section] IN (' + @cols + ')) AS   finalpivot'

It works for just one column and gives column names like this [1],[6],[7]. But it does not work for multiple columns like I need. Any ideas?


texts: How to normalize a table with multiple cells that have multiple values?

texts:
So I'm doing a task where I take a massive list (30,000+) of movies on Wikipedia that have multiple columns (such as the films name, the genre, the cast, the plot etc.) and upload it into Elasticsearch. However, after doing that I now want to make it so the table is in at least 1NF. I'm not really experienced in database design and the last time I did anything with Normal Form was a few years ago. So I'm looking at this table and thinking, how could I put this into 1NF. It's easy if for example there's only 1 column that has multiple values, but what do you do when there are multiple columns with multiple values as seen below.

Film Name
Director
Cast
Genre
Wiki Page
Plot

Chimmie Fadden Out West
Cecil B. DeMile
Victor Moore
Comedy, Western
https://en.wikipedia.org/wiki/Chimme_Fadden_Out_West
Chimmie is sent out west...

20,000 Leagues Under the Sea
Stuart Paton
Lois Alexander, Curtis Benton, Wallace Clarke, Allen Holubar
Action, Adventure
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

The Cat and the Canary
Paul Leni
Laura La Plante, Forrest Stanley, Creighton Hale
Comedy, Horror, Mystery
https://en.wikipedia.org/wiki/The_Cat_and_the_Canary_(1927_film)| In a...

Would you just have to do something like this...

Film Name
Director
Cast
Genre
Wiki Page
Plot

Chimmie Fadden Out West
Cecil B. DeMile
Victor Moore
Comedy
https://en.wikipedia.org/wiki/Chimme_Fadden_Out_West
Chimmie is sent out west...

Chimmie Fadden Out West
Cecil B. DeMile
Victor Moore
Western
https://en.wikipedia.org/wiki/Chimme_Fadden_Out_West
Chimmie is sent out west...

20,000 Leagues Under the Sea
Stuart Paton
Lois Alexander
Action
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

20,000 Leagues Under the Sea
Stuart Paton
Lois Alexander
Adventure
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

20,000 Leagues Under the Sea
Stuart Paton
Curtis Benton
Action
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

20,000 Leagues Under the Sea
Stuart Paton
Curtis Benton
Adventure
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

20,000 Leagues Under the Sea
Stuart Paton
Wallace Clarke
Adventure
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

20,000 Leagues Under the Sea
Stuart Paton
Wallace Clarke
Action
https://en.wikipedia.org/wiki/20,000_Leagues_Under_the_Sea_(1916_film)
A strange...

etc? I'm surely missing something extremely simple when it comes to converting a table with multiple cells with multiple values into 1NF, but I'm not sure what.
Thanks.


texts: Simple Query optimization using ORDER BY

texts:
The following is a simple query that is taking more than 10 seconds to execute:

select * from inventory.asset ass  where ass.product_id=83  and ass.expiry_date > now()  and ass.status=1  order by ass.expiry_date ASC, ass.serial_number ASC limit 10 for update

create table asset (
        id  serial,
        action_date timestamp not null default current_timestamp on update current_timestamp,
        curramt int check (curramt >=0),
        features text,
        initamt int check (initamt >=0),
        opfield1_value varchar(24),
        product_id bigint unsigned not null,
        serial_number varchar(255) not null,
        status tinyint unsigned not null,
        version int,
        export_id bigint unsigned,
        import_id bigint unsigned,
        expiry_date date not null,
        primary key (id),
        unique (serial_number, product_id)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;

create index ix_asset_import_id on asset (import_id);

create index ix_asset_expiry_date on asset (expiry_date);

create index ix_asset_export_id on asset (export_id);

create index ix_asset_serial_number on asset (serial_number);

create index ix_asset_product_id on asset (product_id);

alter table asset
    add constraint fk_asset_import
    foreign key (import_id)
    references shipment(id);

alter table asset
    add constraint fk_asset_export
    foreign key (export_id)
    references shipment(id);

EXPLAIN results:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

'1', 'SIMPLE', 'ass', 'ref', 'fk_asset_status,ix_asset_expiry_date,ix_asset_product_id', 'fk_asset_status', '1', 'const', '2001344', 'Using where; Using filesort'

EXPLAIN EXTENDED results:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra

'1', 'SIMPLE', 'ass', 'ref', 'fk_asset_status,ix_asset_expiry_date,ix_asset_product_id', 'fk_asset_status', '1', 'const', '2001344', '100.00', 'Using where; Using filesort'


texts: Organizing and assigning privileges to multiple objects in MySQL

texts: I'm new to MySQL, moving from MS SQL Server, and I'm wondering if there's a way to logically group tables, views, sp's, etc in the same manner as you would with schemas in SQL Server....seeing as how the concept of a schema is entirely different in MySQL.  In SQL Server I was able to use both schemas and roles to easily manage permissions.  I've found that I can create roles in MySQL and suppose I could write a script that grants permissions on a gazillion objects that way.  Just wondering if there is a cleaner, easily maintained approach.  Am I missing something?


texts: AG (SQLServer 2014) - Suspend Data Movement

texts:
I have a primary replica and one secondary. Multiple availability databases. Secondary replica is located at DR site.

For DR testing, is it possible to failover to the secondary replica, make (test) data changes on the secondary (now primary) replica and then failback but without moving back the test data?

I was thinking this may be possible through suspending data movement i.e.

Suspend data movement
Failover to secondary (DR server) replica
Insert test data
Failback to primary without synchronizing test data
Resume data movement


texts: Incremental backups: MySQL Enterprise Backup vs Percona XtraBackup?

texts:
We're evaluating two tools incremental backup: MySQL Enterprise Backup and Percona XtraBackup. The only comparison I found is by Percona which has obvious cause for bias From this comparison, Percona XtraBackup is better suited for incremental backups as it is faster and supports streaming compression.
Which solution is likely to be more reliable and less of a load on the server.

I would like to understand if there can be trouble when working with one of these tools.
For example, I accidentally stumbled upon "Note" on this page which says that under high load on the server, creating a backup can, in principle will fail.

Load on the server. This is especially important for Backup-point-in-time, because it will allow you to execute it more often.


texts:
What is an Oracle ASSEMBLY, in the context of Oracle SQL?

It's listed in GV$SQLCOMMAND.  Although GV$SQLCOMMAND is not a perfect guide to "real" SQL statements - for example it has UPSERT instead of MERGE.

   INST_ID COMMAND_TYPE COMMAND_NAME             CON_ID
---------- ------------ -------------------- ----------
         1          215 DROP ASSEMBLY                 0
         1          216 CREATE ASSEMBLY               0
         1          217 ALTER ASSEMBLY                0

The commands work although I'm not sure what they are doing.  Oddly the syntax requires a /, implying these objects may contain PL/SQL.

SQL> create or replace assembly some_assembly is 'some string';
  2  /

Assembly created.

SQL> select * from all_assemblies;

OWNER           ASSEMBLY_NAME   FILE_SPEC       SECURITY_L IDENTITY        STATUS
--------------- --------------- --------------- ---------- --------------- -------
JHELLER         SOME_ASSEMBLY   some string     SAFE                       VALID

The data dictionary views work and are documented: ALL_ASSEMBLIES.

There are some Oracle products that use assemblies but I don't know if they tie directly to this SQL statement.  For example, there is the Oracle® Fusion Middleware Using Oracle Virtual Assembly Builder, Database assemblies in Oracle Enterprise Manager, and Database Extensions for .NET (which involve assemblies).

What is an ASSEMBLY?  A deprecated command?  An unimplemented or undocumented feature?  Or a real command that's documented somewhere I haven't looked?


texts:
I have a table on a mysql 5.7 db, containing say athletes with their mean, max, avg times in a specific sport.

I have another table that lists some calculated statistics based on those values.

I managed to do the calculcations that end up on the second using stored procedures.

I use as input parameter to the stored procedure the athlete's name.

So when in the first table, an athlete is inserted (with his/her avg/min/max times) or his/her values are updated and I run the stored procedure, the later updates the statistics table.

My question is how to achieve the same result with triggers.

I guess it is feasible/easy to update the entire table on each insert or update of the first table.

What would be more efficient performance-wise, would be on each

INSERT into table1 values (..) where athlete_name="John Do"

(...)

ON DUPLICATE KEY UPDATE (...)

run a trigger in the pseudocode form

INSERT into statistics_table values (..) where athlete_name="John Do"

ON DUPLICATE KEY UPDATE (...)

How can the the athlete_name="John Do" be passed to the trigger dynamically, to avoid update the entire statistics table?


texts:
I'm new to Postgres and I am trying to implement it through the command line. I just used it previously in a project, but that was by downloading Postgres.app and Postico. I wanted to go through the process of setting up a database through the command line. I installed postgres using Homebrew, and that was fine. I'm trying to follow this tutorial:

https://www.codementor.io/@engineerapart/getting-started-with-postgresql-on-mac-osx-are8jcopb

For some reason, none of the wrapper commands work for me. I wasn't able to use createuser, but running CREATE ROLE patrick WITH LOGIN PASSWORD 'quoted password' was fine.

When I run the \du command, I get the following:

     Role name     |                         Attributes                         | Member of
-------------------+------------------------------------------------------------+-----------
 patrick           | Create DB                                                  | {}
 patrickshushereba | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Which I believe is what I want. Because I created an account that isn't a superuser account.

I also ran \list to make sure that the databases that I wanted to use were created correctly.

  List of databases
        Name        |       Owner       | Encoding | Collate | Ctype |            Access privileges
--------------------+-------------------+----------+---------+-------+-----------------------------------------
 bridgeworq         | patrick           | UTF8     | C       | C     | =Tc/patrick                            +
                    |                   |          |         |       | patrick=CTc/patrick
 bridgeworq_testing | patrick           | UTF8     | C       | C     | =Tc/patrick                            +
                    |                   |          |         |       | patrick=CTc/patrick
 postgres           | patrickshushereba | UTF8     | C       | C     |
 template0          | patrickshushereba | UTF8     | C       | C     | =c/patrickshushereba                   +
                    |                   |          |         |       | patrickshushereba=CTc/patrickshushereba
 template1          | patrickshushereba | UTF8     | C       | C     | =c/patrickshushereba                   +

The first two in the list are the ones that I want to connect to. I'm using Knex.js and node.js to build my backend.

Here is the development object inside of my knexfile:

development: {
    client: 'pg',
    connection: {
      filename: 'postgresql://localhost/bridgeworq'
    },
    migrations: {
      directory: './data/migrations'
    },
    seeds: {
      directory: './data/seeds'
    }
  }

My problem is that when I try to run my migrations, I get the following error:

error: database "patrickshushereba" does not exist.

I haven't found any good resources to troubleshoot what is wrong. What else should I be doing in the command line to make sure that the user that I want to use patrick is connected to the database that I want to use? And if anyone is by chance familiar with Knex, is that the right way to connect to the database? Or do I need to be using a different path? Sorry for the long winded amateur question. But I'd really like to learn how to do this, and what the workflow is for setting stuff up, and I wanted to be thorough. Any help or resources would be greatly appreciated.


texts: SQL Server Errorlog file not releasing space when recycled

texts:
I had a series of SQL Server errorlog (1 to 6) files taking up 39GB.  I needed the space for database growth.  Being a belt and braces sort of guy, I zipped them (or moved them to compressed folder) first.  This reduced the 39GB to 1GB.  I therefore expected to see my disk free space increase by 38GB.  But no!  Stayed exactly the same.

I tried restoring them back, but this didn't complete as there was only the original 10GB free and not enough space for an extra 38GB.  I recycled the logs 6 times using sp_cycle_errorlog in case SQL had somehow kept hold of them.  This reduced the size to a handful of KB as expected, but still made no difference to the disk free space.

Never seen anything like this before.  Anyone any ideas?

SQL 2012, Windows 2012, hosted on VMWare.


texts:
I work in Transportation/Freight and we have these things called "PRO" numbers or freight bill numbers, which you can think about as and Invoice and Tracking number for a shipment.

Our carriers send us blocks of 2,000 sequential PRO #s at a time. Previously, we'd just work from Excel and mail-merge basically just printing off the barcode and changing the cell's color to keep track of what's been used.

We want to automate this process now in the system that creates the shipping documents. Creating the list of PRO #s in MySQL is easy. The problem is that each PRO # can only be used once, but we have multiple people generating shipping documents at any given time.

How can I retrieve a single PRO # from the database and be certain I've only ever used it once?

I'm open to other solutions that are not SELECT based. Maybe I can game the AUTO_INCREMENT system MySQL uses and use an INSERT based system? That would possibly be easiest to implement but I think it could possibly cause problems and could possibly get quite messy and tedious to maintain.

EDIT:
This is a Multi-Tenant system and transactional as well.


texts: InnoDB - Use combined index with primary key on GROUP BY

texts:
As far as I know, an InnoDB index always contains the primary index.

I cannot get the following to work:

SELECT t1.a FROM table_1 t1 LEFT JOIN table_2 t2 on t1.id = t2.refid
WHERE t1.b = 99
GROUP BY t1.id
ORDER BY t1.c

I have these keys:

t1.id = PRIMARY KEY
(b,c) = key

When I EXPLAIN this I get "Using where; Using temporary; Using filesort".

I do use my index on (b,c) but it doesn't use the primary index in that index to get rid of the temp table and filesort.

When I change the GROUP BY to GROUP BY c, I get rid of the temp table and filesort.

Added the primary key field to key (b,c) did not help either.

What am i doing wrong?

Update

Actual query:

SELECT q.id AS q__id, q.user_id AS q__user_id, q.title AS q__title, q. NAME AS q__name
     , COUNT(r.id) AS r__0
FROM questionnaire q
  LEFT JOIN response r
    ON q.id = r.questionnaire_id
WHERE ( q.user_id = 2 )
GROUP BY q.id
ORDER BY q.updated_at DESC ;

Tables:

CREATE TABLE `questionnaire` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `title` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(18) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci,
  `thankyou` text COLLATE utf8_unicode_ci,
  `status` enum('inactive','active') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'inactive',
  `published_at` datetime DEFAULT NULL,
  `license_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Unique_name_idx` (`user_id`,`name`) USING BTREE,
  KEY `questionnaire_license_id_license_id` (`license_id`),
  KEY `deleted` (`deleted_at`),
  KEY `updated_deleted` (`user_id`,`updated_at`) USING BTREE,
  CONSTRAINT `questionnaire_license_id_license_id`
    FOREIGN KEY (`license_id`)
    REFERENCES `license` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `response` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `questionnaire_id` int(10) unsigned NOT NULL,
  `ip` varchar(39) DEFAULT NULL,
  `headers` text,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `response_questionnaire_id_questionnaire_id` (`questionnaire_id`),
  CONSTRAINT `response_questionnaire_id_questionnaire_id`
    FOREIGN KEY (`questionnaire_id`)
    REFERENCES `questionnaire` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

Explain for GROUP BY Id
Explain for GROUP BY update_at


texts: How to change column name in mysql if column name has space?

texts:
ALTER TABLE studentrecord CHANGE COLUMN  Full name fullname VARCHAR(255) NOT NULL;

I just want to change 'Full name' to 'Fullname'


texts: Separate Address Table, Latitude, Longitude

texts:
We are designing a DB for a limited functionality social networking application. Users (Registered Users), Retail Outlets, etc are several entities we need to store. We need to store Address of each of these entities. Also we need to store latitude and longitude of Registered Users, Retail Outlets, etc.

Initially we designed the tables as shown below. We created a separate Address tables as the format of the address will be same for Users, Retail Outlets and other entities.

User(UserID, UserName, AddressID)
RetailOutlet(RetailOutletID, OutletName, AddressID)
Address(AddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude)

Now we realized that we need to search for Retail Outlets near to User's current location(CurrentLatitude, CurrentLongitude)
So we would need to run a query like

SELECT OutletName
from RetaileOutlet
INNER JOIN Address ON RetailOutlet.AddressID = Address.AddressId
WHERE Address.Latitude BETWEEN CurrentLatitude - 10 and CurrentLatitude + 10 and
      Address.Longitude BETWEEN CurrentLongitude - 10 and CurrentLongitude + 10

Now we think that above query would run slow because the number of users will be much more than the number of retail outlets. From DB Design/Performance perspective which of the following should be done.

Create index on Latitude, Longitude in Address table and on AddressID in RetailOutlet table. So there would be no change in table structure.
Move Latitude, Longitude from Address table to RetailOutlet and User tables. Also create index on Latitude, Longitude in RetailOutlet table. So the table structure would be as shown below.

User(UserID, UserName, AddressID, Latitude, Longitude)
RetailOutlet(RetailOutletID, OutletName, AddressID, Latitude, Longitude)
Address(AddressID, CityID, AddressLine1, AddressLine2)

Change table structure as shown below and create index on Lat, Lng in UserAddress table and on UserAddressID in User table.

User(UserID, UserName, UserAddressID)
UserAddress(AddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude)
RetailOutlet(RetailOutletID, OutletName, AddressID)
RetailOutletAddress(RetailOutletAddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude)

Introduce DB redundancy by copying Lat, Lng from Address table in the RetailOutlet table and create index on Lat, Lng in RetailOutlet table. With this option there will be no change in Address table and we will be able to use it for storing User address without change. Also we may be able to use it for storing Address of other entities.

User(UserID, UserName, AddressID)
RetailOutlet(RetailOutletID, OutletName, AddressID, Latitude, Longitude)
Address(AddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude)


texts: What settings control the maximum number of connections per user in Oracle 12c?

texts:
We have some software that is throwing an exception "Pooled connection request timed out"

I know the reason for this and i know the fix. I just need to buy some time on the production server until i can release the software update.

Currently the software establishes about 640 simultaneous connections before it starts throwing this exception.

What database settings can i change which will increase the amount of simultaneous connections?


texts: Why would "Drop Database" fail on local SQL Server 2005 Express instance?

texts:
I am trying to run the below code. It works for me but not for at least some of my users it doesn't work. It doesn't seem to error just no actually remove the DB's.

string sqlConnectionString=@"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
var theSQL="DROP DATABASE DBName1, DBName2, DBName3, DBName4";
SqlConnection conn=new SqlConnection(sqlConnectionString);
Server server=new Server(new ServerConnection(conn));

server.ConnectionContext.ExecuteNonQuery(theSQL);

Alternatively, what I really need is to remove the existing Replication Sets on those DB's so that I can setup the new ones. We moved to a new SQL Server but the database names are the same.

  The subscription could not be initialized using an initial snapshot
  because one or more tables in the subscription belong to another
  publication, and therefore cannot be dropped. Consider initializing
  the subscription manually without using a snapshot.

Error they receive, though this would be after the above was supposed to run as this is during the Sync that happens next.


texts:
I am going to build a flight ticket booking system.
My client has own planes. Under is basic concept of engine.
Admin will add flights.
Passengers, agents, franchises or admin will book the tickets.
They will be developed in PHP with MySQL.
On each seat book request, I want to ensure every time that system not allocating already booked seat or system not allowing book seats more then actual number of seats in a plane.


texts: Database query: join tags with exact matches or at least one

texts:
Given I have a query like:

SELECT count(mm.id) AS c
FROM merchant_mobile mm
INNER JOIN mobile m ON mm.mobile_id = m.id
LEFT JOIN merchant_mobile_customer mmc ON (mm.id = mmc.merchant_mobile_id)
INNER JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
INNER JOIN tag t ON t.id = mmt.tag_id
WHERE mm.merchant_id = ?
  AND mm.status LIKE ?
  AND (mm.last_promo_sms_date <= ?
       OR mm.last_promo_sms_date IS NULL)
  AND t.name = ?
  AND t.name = ?
ORDER BY mm.last_promo_sms_date ASC

I need to perform queries to select:

Matches for exact tag combination: eg: pretty AND blonde
Matches for any combination, but at least one of them: pretty OR blonde

Is it possible to make with just one query?
Do I need a subquery?
Is my schema ok?


texts:
We have a corrupt MyISAM table in our production MySQL database.  Is there a method for repairing this table without locking it?

We were considering using a method similar to Facebook's Online Schema Change or Percona's pt-online-schema-change.  The idea would be to: (1) copy the table, (2) monitor changes to the original using triggers, (3) repair the copy, and (4) finally, swap the repaired table and the original by renaming them.

Will something like this work or is there another recommended technique for repairing a corrupt table in a production database without downtime?


texts:
In SQL Server 2016, I believe the best datatype for handling JSON is nvarchar.
Most of our JSON strings will not use more than 100 characters.
Should I use nvarchar(100) or nvarchar(max)?

We prefer nvarchar(100). I just want to make sure the new JSON functions do not have a preference for using the max type. Max is stored outside of row and slightly less efficient performance-wise.


texts: Why would SQL Server remain unresponsive after a slow query completes?

texts:
I've just finished debugging a problematic SQL Server 2012 table-valued function.  It was declared to use a unique column in the table:

CREATE FUNCTION [dbo].[myApp_getUserIdsFromXml]
    (
    @searchXml xml
    )
RETURNS @tbl_found TABLE
    (
    userId int UNIQUE
    )
AS ...

The function returns some ID values based on XML data.  It was being OUTER APPLIED by various procedures without issue, until in one situation it returned about 60,000 rows, in which case the procedure execution time took 18 minutes instead of a fraction of a second.

The function was debugged and redeclared as:

RETURNS @tbl_found TABLE
    (
    userId int PRIMARY KEY /* <-- PK fixed the issue */
    )
AS ...

The execution time then dropped to 0.68 seconds.

Before debugging and altering it, even after the query had completed (18 minutes' worth), SQL Server remained extremely unresponsive.  Simple queries took minutes rather than milliseconds, and the SSMS GUI continuously froze and showed Windows' not responding message.  This lasted for about an hour each time before SQL seemed to assume normal speeds.

Can anyone suggest a reason why this might happen (e.g. memory not being freed, or something).  I've been using SQL for over ten years and never seen behaviour like this (the behaviour was replicated on two servers).

Or, can anyone suggest TSQL code to help investigate different areas where the problem might lie?  I've found code to test for locks, but there was nothing 'locked' here, the transaction had completed and nothing was being executed (by my apps) at the time.

It's obviously fixed now, but I've lost too much hair in the process...


texts:
I am looking for a way to monitor Oracle database status (health) via PHP script executing SQL query. There are system variables containing actual number of connection, sessions, work load, etc...

How I can access to those values: via regular SELECT statement, or built-in stored procedure, or it need to be custom build stored procedure...

How I can detect, after I get those values, is it a normal, heavy, extreme work load. What are the criteria?


texts: Getting upcoming world wide releases from a database


texts: is possible to use graphql with couchdb?

texts: I work well with graphql environment, and I need to use couchdb, but his api with uris like rest api, it's for me a little old way to retrive data. Is possible to use graphql with Couchdb ?  or there are plans in the future ?


texts: Are problems with parallelism more common today? What issues can arise from parallelism?

texts:
This is a very general question so I don't give much specific information. I am more interested in parallelism and its possible drawbacks. Generally I am all for parallelism as something useful and positive.

I seem to come across more and more systems that have one or more stored procedure that have issues with too much parallelism. Is there a good way to find out if that really is the problem or if it is something else lurking about?

Usually the sql is a bit complex with maybe 5 or more joins and also some udf and even more complexity. So far I found these issues by examining everything from usual performance issues but also from more extreme situations with error messages like "Could not create thread..." and also deadlocks from multiple simultaneous copies of the same store procedure.

I am really curious how you guys do to find issues like these and also how you go about solving them. Recoding? Playing with MAXDOP (max degree of parallelism)? And so on...


texts: MySQL - Force index degraded performance over time

texts:
Last year we migrated data from our old product environment into a new environment with our new product.
Since the new product was still fresh, we found that some of the new queries were very slow after the migration. and we fixed several of them by adding the 'FORCE INDEX(SOME_INDEX)' to the queries join clauses. this immediately solved our problem and sped up the queries dramatically.
Lately we see some performance degradation in the new product again, and the same queries we optimized last year using the 'FORCE INDEX(SOME_INDEX)' are not faster without the force index part of it (basically using the original query from last year is now faster)
Someone at the office threw to the air the idea that mysql keeps his own statistics of its indexes and as the mysql server keeps working it become more efficient in creating the flow of the query. (meaning that at the beginning it has less statistics and thats why hinting to force index helps it, and after a year of production usage its own statistics are enough).
Is this correct? is there any other plausible explanation for this?


texts: No SQL Server instances show in upgrade setup

texts:
I'm trying to upgrade SQL Server 2005 sp4 to SQL Server 2008 R2. However on the upgrade wizard doesn't show me any instances to upgrade. Any ideas?

The instance is there just fine.  I can connect via sql mgmt studio.


texts: Upgrade from Postgres 11 to Postgres 12 made some queries 300x slower, probably due to new CTE handling

texts:
My application currently uses PostgreSQL 11.6. Today, I have tested PostgreSQL 12.1 on a virtual machine and the results were shocking:
One important query which takes 100ms on version 11 (same VM) now takes about 36s on Postgres 12. That's more than 300 times slower.

My suspicion is, that the new handling of CTEs, namely the MATERIALIZED, NOT MATERIALIZED thing is responsible for that.

If I change every CTE to MATERIALIZED, the query goes down from 36s to 6s. Significantly better, but still more than 50 times slower than in version 11.

If I get it right, in PostgreSQL 12 you have two alternative options:

with MATERIALIZED the CTE is just executed once but you lose the benefit of indexes
with NOT MATERIALIZED you get the benefit of indexes, but your CTE gets executed each time its results are accessed.

Is that correct?

Is there any trick, e.g. a special setting to go back to the Postgres 11 behavior?
Or is the only way to handle this manually evaluating each and every CTE if MATERIALIZED or NOT MATERIALIZED is better?

Quite often, I guess, it is not clear which way is better. My application contains hundreds of CTEs, many of which do both table queries and expensive function calls (the example in the docs where they say that NOT MATERIALIZED is better).

Edit:
What I have checked for making results comparable:

Same Virtual Machine
Same and Very small dataset
Same postgresql.conf
Re-indexed
vacuum analyze

Results of EXPLAIN ANALYZE:
Postgres 11
Postgres 12


texts:
I've been struggling to implement Oracle's network encryption between two of my test boxes.  I think I've finally been able to do it, but I can't seem to find a way to verify that it is indeed enabled.

I know that tracing is enabled by default on the server side, but is there a command I can run on the remote sqlplus instance that will generate a trace and allow me to see if the specific sql is encrypted?  Is there a better way to test this?

Thanks everyone!


texts: Wordpress blog where pages are almost-always served from cache: SSD or more RAM?

texts:
I am working on the design of a Wordpress blog which when finished will almost-always serve pages from disk cache and this is because all posts will be published in batch and then cached, with at least 5 hours between batches.
Database size is only 30MB at the moment as this is a new blog.
My server options are two:

a. Either a 4GB RAM server with an SSD or

b. An 8GB RAM server with a normal HDD.
Am I right in assuming that since pages will almost-always be served from disk cache, SSD is a better option?
Or maybe I should still go for more RAM?
I ask because years ago I worked on a blog generating almost 2m pageviews per month and when it was crashing we identified the heavy HDD usage as the problem. In fact after tweaking innodb_buffer_pool_size so as to use more RAM and less disk, we never had any more issues.
Thank you.
Edit By RolandoMySQLDBA
Please run this query
select var,concat(numunit,' ',unit) size from
(
    select var,format(num/power(1024,ex),2) numunit,SUBSTR(units,ex*2+1,2) unit
    from
    (
        select var,num,FLOOR(LOG(IF(num=0,1,num))/LOG(1024)) ex
        from
        (
            select variable_name var,variable_value*pagesize num
            from information_schema.global_status AAA,
            (
                select variable_value pagesize
                from information_schema.global_status
                where variable_name='innodb_page_size'
            ) BBB
            where AAA.variable_name like 'innodb_buffer_pool_pages%'
        ) AA
    ) A,(select 'B KBMBGBTB' units) B
) M;

and post its output so was can see the Buffer Pool's current usage.
Also, please run SELECT VERSION();
Edit
The blog is still on the development machine so I believe the first query is of no use. I can provide the result of the second though:
+------------+
| VERSION()  |
+------------+
| 5.6.16-log |
+------------+
1 row in set (0.03 sec)

It will be the very latest version when we go in Production.


texts: is Sql Server AlwaysOn AG a multi-site active-active replication technology?

texts:
Scenario FCI: I understand multi-site active-active replication in SQL Server can be installed with an FCI (Failover Cluster Instance). The replication part occurs at the storage level with a connected SAN. So it's considered active-active, but only one node has the FCI ownership, but the data is replicated between sites at the storage level.
Scenario AG: But, I'm searching for a widely accepted definition of active-active replication. In Microsoft's documentation, the secondary replica in an AG (Availability Group) is active if the same can be used for read-only queries. So, an AlwaysOn AG could be used to get replication between the two sites, with the primary active read-write and the secondary active-read-only.
So the difference is:

Scenario
Main Site
DR Site

FCI
read-write
read-write on failover

AG
read-write
only-read  without failover

So my question is: I'm seen that FCI with synchronous storage level replication is considered active-active replication.  But, I'm seen too that active-active replication is considered only when you can read-write to both sites. So, my questions are:

is there some widely accepted definition of active-active replication?
Scenario FCI and scenario AF are considered active-active replication?
what's the difference between active-active replication and master-master replication?


texts: Advice on transaction log based reporting server

texts:
I am in need of advice on setting up a remote read-only MSSQL 2008 reporting server using transaction logs.

From what I gather, true TLS setup requires both database servers to be aware of each other however in my case the server where the logs will be applied is in no way connected to the primary server.

I am using the following steps to replicate data to the reporting server approximately every 15 minutes.

Download incremental or full backup file posted to a SFTP server
Put database into single user mode
Apply backup (with replace, standby)
Put database into multi user mode

The problem I am having is the database is unavailable to applications during the period when the database is in single user mode. Is there a better solution for what I am attempting to accomplish where the database wouldn't need to refuse connections, or could queue connections until the backup has been applied?

Thanks!


texts: Read only/Standby database - Set user permissions

texts:
Example database: WarehouseDB
Example user: reporting
Database state: Read only/Standby mode

How do I give the user 'reporting' access to only WarehouseDB without giving them sysadmin privileges?

The problem occurred when I restored a bak file to create WarehouseDB in norecovery mode and then restored another transaction log file to put it in read only/standby mode for doing select queries for reporting purposes. And the reason I do log shipping then onwards is because both production and warehouse database can be synced at all times without any manual intervention. Now if I give this user sysadmin permissions then of course they can query the database but due to security concerns, I can't allow them. Also because it's a read only database, I can't create another user either and map them to WarehouseDB.

Another thing I forgot to mention is, the source database name is for example 'ProductionDB' and when restoring it's changed to WarehouseDB because I have mirroring setup for ProductionDB on the mirrored server where WarehouseDB is then restored.

Any ideas on how to resolve this?


texts:
What is the proper indexing plan for the below query?
Query 1 :
select id
from room_participants
where roomId=1
  and userId=1
  and status=1
  and isMuted=0

Query 2 :
select group_concat(b.phone)
from room_participants as a
left join users as b on b.id=a.userId
where a.roomId = 1
  and a.status = 1

Is it an advisable way to do indexing as per my example below? Does this is what we called redundant index as index combine3 is having the same column as index combine4 and index combine2 is having the same column as index combine1?
CREATE TABLE `room_participants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roomId` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `isAdmin` tinyint(1) DEFAULT '0',
  `isMuted` tinyint(1) NOT NULL DEFAULT '0',
  `joinTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `leftTime` timestamp NULL DEFAULT NULL,
  `kickTime` timestamp NULL DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=join, 2=kicked, 3=left',
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  KEY `combine2` (`isAdmin`,`userId`,`joinTime`,`leftTime`,`kickTime`,`status`),
  KEY `combine1` (`kickTime`,`userId`),
  KEY `combine3` (`roomId`,`userId`,`status`,`isMuted`),
  KEY `combine4` (`roomId`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=567 DEFAULT CHARSET=latin1;

Please guide me on this. Thanks.


texts: Why "Select * into targettable from sourcetable “ is faster than “insert into targettable select * from sourcetable

texts:
This title is the question. I am curious to know the answer. Someone told

select into is minimally logged in Simple recovery model database ... I didn't get into it at all.

Excerpt from Microsoft:

The amount of logging for SELECT...INTO depends on the recovery model in effect for the database. Under the simple recovery model or bulk-logged recovery model, bulk operations are minimally logged. With minimal logging, using the SELECT… INTO statement can be more efficient than creating a table and then populating the table with an INSERT statement

Seeking help

Thanks


texts: Problem replicating IDENTITY columns from Sybase ASE 15.5 to Sybase ASE 15.5

texts:
I am trying to perform replication from Sybase ASE 15.5 to Sybase ASE 15.5 for a list of given tables via replication definitions and subscriptions defined in Replication Server(REP) Version 15.7.1. I found out that there is a table with one of the columns defined as IDENTITY. After creating the replication definition and subscription for that table, I am getting the below error messages in replication server:
Message from server: Message: 584, State 1, Severity 16 -- 'Explicit value specified for identity field in table 'customers' when 'SET IDENTITY_INSERT' is OFF'.
I have tried to run SET IDENTITY_INSERT customers ON on both of my primary and replicate database, but no luck.
Appreciate if you could help. Thanks!


texts: permissions for schema compare and view job history

texts:
I need to grant these permissions,
what would be the minimum level I could grant so that they can do their job?

I am looking at SQL Server Agent Fixed Database Roles
and also Database Engine Permission Basics

I still could not find exactly,
what would be the least permissions to be granted?


texts: Single MySQL query with row averages based on conditions

texts:
I have problem, where i know how to proceed with multiple queries, but dont really now

I Have a table with

  BP {
  name: varchar
  systolic : int
  diastolic : int
  timestamp: date
  }

I need to get the Names from BP table that satisfy the following ANY of the following conditions.

If  systolic > 180 OR diastolic > 110
If  (systolic  >= 140 AND systolic < 180), then take the next 2 readings of systolic, get the average and if that average is >= 140. Then Condition Satisfied.
Same diastolic. If  (diastolic  >= 90 AND systolic < 110), then take the next 2 readings of diastolic, get the average and if that average is >= 90. Then Condition Satisfied.

There will be more many rows with the same unique name.  (meaning many BP Recordings per person).

I can certainly get this working if i jus get all the values and parse it myself in PHP.

But Im wondering if theres a better way to do it directly in MySQL.

My goal here was to find the people that have Hypertension. And those conditions above the guidelines in categorising a person as Hypertensive.

In this example, I simplified the table. And I want to get the name(s) of those that meet these hypertensive guidelines.

Condition 1 is very simple to address. Condition 2 & 3 are whats troubling me.

What I do now

I just get all those values using SELECT and ORDER BY timestamp ASC, name. And parse everything in PHP
I simply get the list of people with systolic >=140 AND systolic < 180, Filter them out based on name, order it by timestamps and enumerate through each set, if I find a systolic >= 140 AND systolic < 180, i break enumeration and check the next 2 readings of that name and take the average.
I repeat that with diastolic. and Merge the results with all the three conditions, removing any duplicate entries.

I really want to learn if there was a good way to do this in sql itself. Im assuming using a Stored Procedure would be a must. But I'd be glad to know if there was a better way using some kind of subquerying.


texts:
I'm not a developer but a system administrator,
I found this query SELECT * FROM bogus_table LIMIT 0\G in the MySQL-slow-query.log in the production server.
I asked the developer about this query and he has no idea where this query coming from.
Thank to this post i assume that SELECT * FROM bogus_table LIMIT 0\G is to clear the kill flag which mean this query will be trigger afterward if the previous query return an error.- correct me if i'm wrong.
So here my question: How do i find/trace what query that running is triggering this query to be executed afterward (Based on my above assumption) SELECT * FROM bogus_table LIMIT 0\G ?
Thank you.


texts: Suggested SQL Server Backup Schedule using Ola Hallengren scripts

texts:
We are building out a new AlwaysOn SQL Server 2016 database cluster and I am in the middle of setting up the backups using Ola Hallengren (https://ola.hallengren.com) backup scripts and I was looking for some advice when best to run the the following tasks:

DatabaseIntegrityCheck - SYSTEM_DATABASES
DatabaseIntegrityCheck - USER_DATABASES
IndexOptimize - USER_DATABASES

I am currently planning to use the following schedule for the backups:

DatabaseBackup - SYSTEM_DATABASES - FULL @ 23:30 every day
DatabaseBackup - USER_DATABASES - FULL @  23:00 every day
DatabaseBackup - USER_DATABASES - DIFF @ Every hour starting at 00:00 to 22:00
DatabaseBackup - USER_DATABASES - LOG @ Every 5 mins starting at 00:03 to 23:59:59

By reading the suggestion was to do the database integrity check before the backups so I was think maybe 30 mins before the main backup and then run the index optimize when a known time of little load. Would that make sense as a schedule for SQL Server backups?


texts:
I want to know how can I change the recovery model of my current database. I am having sysadm rights.Operating system is windows, Db2 10.5.

can some one let me know the command please


texts: How SQL Server determines the data transfer rate

texts:
We are having some issues with the data transfer rate when extracting data from SQL Server. Our scenario is as follows:

SQL Server 2008, in a Windows Server 2003 with 32 GB RAM (located in Guatemala).
Windows Server 2012 R2, in a EC2 instance in AWS (Virginia USA), with 16 GB RAM.
For extraction we are using Pentaho Spoon, extracting ~13 tables at the same time.

The issue is because sometimes tables are loaded fast, but sometimes one or two tables are loading very slow. Looks like the data transfer sometimes is really bad for those tables, transferring at 50 rows/s, instead of 2,000 rowd/s (should be normal).

Almost all the times, SQL Server shows in the Activity monitor a Wait Type: ASYNC_NETWORK_IO, which I guess is due the network activity. Even when the data is loaded fast, that wait type is displayed.

How SQL Server determines the transfer rate for each table?

Is it based on indexes or network activity?

The application which is consuming the data, is processing in batches of 5000 rows. It is receiving the data and inserting into a stage database every 5000 rows, even inserting into a flat file. Therefore, I don't think the application is processing the data row by row.

Locally, the performance is better (it's reasonable) but there is always a difference among some tables. Some of them are loaded faster than others. It's weird. I'm not a DBA, so I think it could be something related to indexes? Or something about DB tuning.


texts: Entity-Relationship Conceptual Data Model

texts:
So I am busy learning databases for an rpg I have been thinking of to learn more specialized techniques with game development itself. Now I am trying to draw a conceptual data model to get things solid and clear for me to work with, but I can't quite understand the relationships between two entities.
Take for example this image:

How does this work, I understand the rectangles are entities and therefore become tables in a SQL database but how can those relationships have attributes? Do they become their own tables or is the fk in Account a multivalue with all Character Names?

I can't quite grasp it yet I understand how databases work on a basic level. It just needs to click so any help or explanation will be appreciated.


texts: is keeping idle / unused mysql databases bad?

texts:
Is there any harm like eating up resources etc. Or is mySQL engineered to just work for the used ones and keep others at hand. If so, why is there such thing like taking a db offline? Should I do that?

Thanks


texts: Compare previous column and current column in ORACLE SQL and do SUM

texts:
This is my query to get TIME_DIFFERENT-> TO_CHAR (DATE '1900-01-01' + ABS ( LAG(MODIFIED_DATE,1)OVER(ORDER BY MODIFIED_DATE DESC)  - MODIFIED_DATE), 'HH24:MI:SS') AS TIME_DIFFERENT
Now, I want to do compare if previous row ='LOAD_LOT' and current row ='UNLOAD_LOT , then SUM UNLOAD_LOT(00:00:30 and 00:00:33).  Appreciate someone could help me on this issue. THANK YOU
For example:
EQP_ID        LOT_ID       ACTIVITY     MODIFIED_DATE                 TIME_DIFFERENT
93K-P001      WM33547      LOAD_LOT     9/28/2020 3:22:47 PM
93K-P001      WM33547      LOAD_LOT     9/28/2020 3:17:23 PM           00:05:24
93K-P001      WM31882      UNLOAD_LOT   9/28/2020 3:16:53 PM           00:00:30
93K-P001      WM31882      LOAD_LOT     9/28/2020 10:21:28 AM          04:55:25
93K-P001      WM33547      UNLOAD_LOT   9/28/2020 10:20:55 AM          00:00:33

This is my Query
SELECT RMSTMP_PNG.ota_activity.EQP_ID,
       SUM (CASE WHEN (RMSTMP_PNG.ota_activity.MESSAGE='Load lot success.'
                      AND RMSTMP_PNG.ota_activity_lotinfo.KEY = 'Quantity')
           THEN 1 ELSE 0 END) AS LOT_ID,
       SUM (CASE WHEN (RMSTMP_PNG.ota_activity.MESSAGE='Load lot success.'
                       AND RMSTMP_PNG.ota_activity_lotinfo.KEY = 'Quantity')
           THEN TO_NUMBER(RMSTMP_PNG.ota_activity_lotinfo.VALUE) ELSE 0 END) AS QUANTITY,
       MAX (CASE WHEN (RMSTMP_PNG.ota_activity_lotinfo.KEY = 'TestProgram')
           THEN TO_CHAR(SUBSTR(RMSTMP_PNG.ota_activity_lotinfo.VALUE,1,3))
           ELSE '0' END ) AS Product
  FROM RMSTMP_PNG.ota_activity FULL OUTER JOIN
       RMSTMP_PNG.ota_activity_lotinfo
          ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID
 WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE >= to_date('28-Sep-2020')
   AND RMSTMP_PNG.ota_activity.MODIFIED_DATE < to_date('28-Sep-2020') + 1
   AND EQP_ID ='93K-P001(LOOP)'
 group by RMSTMP_PNG.ota_activity.EQP_ID
 order by RMSTMP_PNG.ota_activity.EQP_ID

I want My final output will become like this:
EQP_ID       TOTAL_LOTID      QUANTITY      PRODUCT      TOTAL SUM_UNLOAD_LOT
93k-P001         2               3           SDR          00:00:63
93k-P002         4               1           FRT          total sum unload-lot


texts:
I have created a Table-Valued Parameters on my SQL Server 2008 R2 install.  I created a field as a varchar(25) and now I need to modify it to a DateTime

What would be the process for doing such?


texts:
I'm getting an error when opening Mysql notifier:

  High Severity Error Element 'UpdateCheck' was not found. Line 3, position 2.

My MySQL server is running. Why is it showing this error?


texts:
We have a customer who is switching from SAN storage to directly attached storage. Our SQL Server database has several filegroups all of which has been deployed on the same LUN.

In order to determine the best setup for the filegroups on the directly attached storage I want to test the loading on the existing file groups. For directly attached storage I would normally have tested loading by looking at performance counters like disk queues etc. but this assumes that the file groups have been setup on separate arrays. For SANs we would normally have got the IO throughput for each LUN from the SAN vendor.

However, because all the filegroups are on the same LUN, how can I test how heavily each filegroup is loaded? Is there any way to do this with SQL profiler?

Thanks
Chris


texts: Replicate a database with a subset of data offered by custom queries

texts:
As seen in this question I can use the following sql command:

CREATE DATABASE newdb WITH TEMPLATE olddb;

In order to replicate a database into my machine. But in my case my new database only to contain a subset of the data used in the first database offered by custom queries.

Lets suppose that in my current db has an existing tables:

|- ohimesama:
|-- id PK SERIAL
|-- name VARCHAR(100)
|
|-oujisama:
|-- id PK SERIAL
|-- name VARCHAR(100)
|
|- ohimesama_oujisama_suki:
|-- ouji_id FK oujisama
|-- ohime_id FK ohimesama

And In my database I want to be able to keep only the records from the tables ohimesama and oujisama that maches the current queries:

SELECT * from ohimesama where id in (SELECT ohimesama_oujisama_suki JOIN oujisama where oujisama="WILLIAM");

SELECT * from oujisama where id in (SELECT ohimesama_oujisama_suki JOIN oujisama where ohimesama="MARRIE");

My database is rather large (~100GB) so truncating or deleting rows from the whole database is rather time consuming. Do you have any Idea how to solve that problem?

As you can quess both of the databases are in the same server. The reason why I want to do that is because I want to keep a subset of data for software development.

Follow up: I have issues setting up the JDBC driver. I get the following error:

No suitable driver for jdbc:postgresql://

...though I have installed the PostgreSQL JDBC driver via gnu/linux apt command and specified the correct directory.


texts: PMA export with all attributes in CREATE TABLE

texts:
This is the default PMA output of an export:
CREATE TABLE `foo` (
  `id` int(11) UNSIGNED NOT NULL,
  `fk_id` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `foo`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `foo`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `foo`
  ADD CONSTRAINT `foo_ibfk_1` FOREIGN KEY (`fk_id`) REFERENCES `bar` (`id`),

...

Is there a way to get an export where all of the following ALTER TABLE are merged into the CREATE TABLE statement?


texts: Find all occurrence of value from column b in column a

texts:
So basically I have 2 tables which I merge by

SELECT t2.columnA , t1.columnB, t1.description
FROM Table1 as t1
LEFT JOIN Table2 as t2 on t1.GIDNumber=t2.TwrNumber

I need to display all occurrence of value from t1.columnB in t2.columnA

The perfect situation for this dataset is when there is no duplicated value in column A and value of column B matches value from column A, but i know there is a lot of errors.

This is example of situation i want to display. The value of ColumnB which is in red box appears in ColumnA multiple times.

UPDATE: Sample data for first query

t2.columnA

5900920132278
5900920132278
5900920132278
5900920132278
5900920132278
5900920132278
038548088099
038548088099
038548088099
038548088099
038548088099
5900442707411
5909426907526
5900855046879
4014855166091
5909426909667

t1.columnB

5900920061554
5900920132278
2010000001165
5900920061479
5900920132261
5900920132278
038548088099
5706915071948
38548088099
038548088099
38548088099
2100000038879
2100000035502
2100000030163
4014855026562
2100000031627

In this dataset need to display all records from t2.columnA contains 5900920132278 and 038548088099

UPDATE 2. Data from separate queries:

COLUMN A:

5900920132278
5900920132278
038548088099
038548088099
038548088099

GIDNumber:

21801
37273
97191
163053
165153

ColumnB:

5900920132278
5900920132278
5900920132278
038548088099
038548088099
038548088099
038548088099

TwrNumber:

21801
37273
91424
21801
37273
91424

Solution :

SELECT t2.columnA , t1.columnB, t1.description
FROM Table1 as t1
LEFT JOIN Table2 as t2 on t1.GIDNumber=t2.TwrNumber
WHERE EXISTS
( SELECT 1 FROM Table2 as t3 WHERE t1.colmnB = t3.columnA )
AND t2.columnA <> t1.columnB


texts: Query that returns a unique ID for each different clause matched?

texts:
I'm in a situation where I'm being submitted a (potentially quite long) list of "match entities", each of which contains user data to match, along with a unique ID for that match information.  Actual user data from matched users, along with the unique ID for that match, needs to be returned from my SQL query.  So, let's say I were given 2 entities to try and match users against, both of which were trying to match phone numbers against a user's phone number; I could match any users associated with the entities submitted along with the "match entity"'s unique ID by using a union like this (client_handle is the submitted unique ID):
SELECT
    [client_handle] = 'axtwe-wasst',
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive]
WHERE
    [mobile_no] in ('+44 7747 122123', '+44 7904 223323')

UNION

SELECT
    [client_handle] = 'zjfft-albwq',
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive]
WHERE
    [mobile_no] in ('+44 7758 444111', '+44 7758 444222', '+44 7758 444333')

The trouble with this method is that it could potentially result in a very large number of UNIONs if large numbers of match entities are submitted to me.  1000 submitted match entities would result in 999 UNIONs.  Is this actually an issue performance-wise, and is there a better way to achieve the result I want?  Alternatively I could just loop through each submitted match entity and run a query to match each, but then I'd have 1000 separate queries if 1000 match entities are submitted, which seems even worse.


texts: Efficient query to get greatest value per group from big table

texts:
Given the table:

    Column    |            Type
 id           | integer
 latitude     | numeric(9,6)
 longitude    | numeric(9,6)
 speed        | integer
 equipment_id | integer
 created_at   | timestamp without time zone
Indexes:
    "geoposition_records_pkey" PRIMARY KEY, btree (id)

The table has 20 million records that is not, relatively speaking, a large number. But it makes sequential scans slow.

How can I get the last record (max(created_at))  of each equipment_id?

I've tried both the following queries, with several variants that I've read through many answers of this topic:

select max(created_at),equipment_id from geoposition_records group by equipment_id;

select distinct on (equipment_id) equipment_id,created_at
  from geoposition_records order by equipment_id, created_at desc;

I have also tried creating btree indexes for equipment_id,created_at but Postgres finds that using a seqscan is faster. Forcing enable_seqscan = off is of no use either since reading the index is as slow as the seq scan, probably worse.

The query must run periodically returning always the last.

Using Postgres 9.3.

Explain/analyze (with 1.7 million records):

set enable_seqscan=true;
explain analyze select max(created_at),equipment_id from geoposition_records group by equipment_id;
"HashAggregate  (cost=47803.77..47804.34 rows=57 width=12) (actual time=1935.536..1935.556 rows=58 loops=1)"
"  ->  Seq Scan on geoposition_records  (cost=0.00..39544.51 rows=1651851 width=12) (actual time=0.029..494.296 rows=1651851 loops=1)"
"Total runtime: 1935.632 ms"

set enable_seqscan=false;
explain analyze select max(created_at),equipment_id from geoposition_records group by equipment_id;
"GroupAggregate  (cost=0.00..2995933.57 rows=57 width=12) (actual time=222.034..11305.073 rows=58 loops=1)"
"  ->  Index Scan using geoposition_records_equipment_id_created_at_idx on geoposition_records  (cost=0.00..2987673.75 rows=1651851 width=12) (actual time=0.062..10248.703 rows=1651851 loops=1)"
"Total runtime: 11305.161 ms"


texts: Resolving a double many-to-many relationship

texts:
I have the following database design between entities.
A Student can belong to many Departments and a Department has many Students. A specific Department has many Courses and one Course can be at many Departments. However, the course has a different relevance value depending on which department it belongs to. The Student have grades on specific courses on specific departments. How should I create the relationships that the Student can have grade attribute?
The many-to-many tables are now like this:
| Student ID | Department ID |
| 1          | 1             |
| 1          | 2             |
| 2          | 1             |
| 2          | 2             |

======================================
| Department ID | Course ID | Relevance |
| 1             | 1         | 5         |
| 1             | 2         | 10        |
| 2             | 1         | 11        |
| 2             | 2         | 4         |

Is it OK if I make a primary key at the many-to-many table between the Department and Course and I connect the Student entity there, as in the image below? And this will lead to something like:
| Student ID | Department ID | Course ID | Relevance | Grade |
| 1          | 1             | 1         | 5         | 5     |
| 1          | 1             | 2         | 10        | 7     |
| 1          | 2             | 1         | 11        | 8     |
| 1          | 2             | 2         | 4         | 10    |
| 2          | 1             | 1         | 4         | 10    |
| 2          | 1             | 2         | 3         | 9     |
| 2          | 2             | 1         | 6         | 9     |
| 2          | 2             | 2         | 7         | 9     |


texts: Can NoSQL databases cause occasional data loss?

texts:
I am currently evaluating databases to use for a new project, which will require insertion and querying of large amounts of trading data. Our team is leaning towards Cassandra, but then I read this article that seems to suggest using non-ACID compliant databases can result in occasional data loss:

  http://www.dbms2.com/2010/09/21/acid-compliant-transaction-integrity/

I can't find any further information on this on the web and can't understand how non-ACID compliance means that data loss can occur. Can anyone shed some light?


texts: MySQL Error: Authentication plugin 'caching_sha2_password' cannot be loaded

texts:
I just installed MySQL Ver 14.14 Distrib 5.7.22 with Homebrew on my macOS v10.13.4.
I ran the command:
brew install mysql

After the installation completed, as directed by Homebrew, I ran the command:
mysql_secure_installation
and was returned the error: Error: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/Cellar/mysql/5.7.22/lib/plugin/caching_sha2_password.so, 2): image not found

I tried a few things like changing default_authentication_plugin to mysql_native_password in the my.cnf file but it still throws the same error.

Next I tried running:
mysql_upgrade -u root
and I was thrown the same error again mysql_upgrade: Got error: 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/Cellar/mysql/5.7.22/lib/plugin/caching_sha2_password.so, 2): image not found while connecting to the MySQL server
Upgrade process encountered error and will not continue.

Any help is appreciated.


texts: How do I add databases from a crashed MySQL 5.1 server to a new MySQL 5.6 server

texts:
I was running MySQL 5.1 on a personal web server that recently died.  The system drive is non-operational, but I have my second hard disk where I had installed all of my programs.  This drive has my MySQL 5.1 installation directory, and in the data subdirectory appears to have all of my databases.  Databases are both MyISAM and InnoDB, mostly the latter.

I just purchased a new PC to use as a server and installed MySQL 5.6 on it.  I would like to move these databases onto this new server.  I had hoped it would be as easy as stopping the service, copying the subfolders within [installDirectory]/data, and restarting the service.  However, when I tested that with a small InnoDB database MySQL started okay and I could log into it and see the database, but all tables reported no rows and gave errors when trying to access them.  MyISAM databases seem to copy over just fine.

How can I get these old databases onto the new server?  I do have sql dump backups, but they're a month old (again, this was a personal web server) so I'd rather not go back to them if I can help it.  All of the resources I can find in a web search detail moving files from a functional 5.1 server to a functional 5.6 server.


texts: I made a back of an express DB, it was 14.7GB big (how is this possible) but anyway, I need to restore it back to express but obviously it is too big, but there must be somehow I can do this. I need to get it restored and then I can shrink it. Please help this is urgent and need to get it up in 1 hour.


texts: How do I control the data that goes into each data node in NDBCLUSTER

texts:
I am currently working with NDBCLUSTER.

I have the following table(a dummy example, since I cannot disclose details about actual application, but the structure is similar):

create table student(
 STUDENT_ID INT PRIMARY KEY NOT NULL,
 BRANCH_ID INT NOT NULL REFERENCES branch(BRANCH_ID),
 FIRST_NAME VARCHAR(100),
 LAST_NAME VARCHAR(100),
)

ENGINE NDBCLUSTER;

In my NDBCLUSTER cluster, I have:
1.

1 management node
6 data nodes
3 MySQL nodes

Let's assume also, that I introduce 6 records in this table. My question is this:

Is it possible to control the data(in this case, the rows) that goes into each data node/partition?
If yes then how, and if no, then why not?


texts: SQL Server 2014 SP1 CU6 Issue with Multi-Valued Parameters (No Longer Expand)

texts:
Since upgrading to SQL Server 2014 SP1 CU6 (from CU4) on two different servers we have encountered what appears to be a bug with multi-valued parameters in SSRS where you can no longer drag to expand them. Also you must reload the page before the drop-down will function again.

The issue seems to only affect IE11 and is ok in Chrome and did not occur before updating from CU4. If we downgrade back to CU4 then the issue is resolved again on each server tested.

I have carried out further tests and I can confirm that the drop-downs are also broken in the same way in CU7 and also SP2 so the issue appears to not have been addressed as of yet by Microsoft.

It would appear someone else is asking about the same issue here:

This is what the multi-valued drop-down looked like before:

This is what the multi-valued drop-downs looks like now:

As you can see the option to drag the selection list has gone and some of these are very long and contain long descriptions and it means there is no way to view all the text at once.

Once the drop-down has been dropped-down once you must refresh the page which is a pain as it obviously clears the selection from all parameters.

I cannot see this raised as a KB article anywhere or many posts with people experiencing this which seems surprising.

Thanks
Robin


texts: .psqlrc - setting keepalives_idle connection parameter

texts:
Is it possible to set keepalives_idle parameter in .psqlrc so that every connection I open uses it?

I've finally found this option and it's a remedy for my connection dropping problems.

I would really like to set it globally for all sessions I open with psql.


texts: How do I run a query only asking for a max date of attendance?

texts: I'm new to SQL and the school I work for wants to run a query on students' transcripts. We want to find out the most recent school the student attended prior to coming to ours. I think I need to use a Max date clause, but I'm not sure. Can someone help me? Thank you in advance.


texts:
Are there any known pitfalls with MySQL partitioning when server behavior differs a lot from the expected one?
I'll try to explain what I mean. Let's create partitioned table and fill it with random data.
CREATE TABLE PartitionedTable (
  id int(11) NOT NULL AUTO_INCREMENT,
  col int,
  PRIMARY KEY (id),
  KEY(col)
) PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (4000),
    PARTITION p2 VALUES LESS THAN (6000),
    PARTITION p3 VALUES LESS THAN (8000),
    PARTITION p4 VALUES LESS THAN MAXVALUE
    );

INSERT INTO PartitionedTable (col)
SELECT FLOOR(RAND() * 1000)  AS col FROM
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4 ;

Now let's check the following queries (I'll use MySQL to be able to use EXPLAIN ANALYZE but it seems execution plan for other MySQL Server versions is the same).
The first query:
EXPLAIN ANALYZE SELECT col FROM PartitionedTable WHERE id >= 6000 AND id < 8000 ORDER BY col DESC LIMIT 1;

Execution plan:
-> Limit: 1 row(s)  (actual time=0.021..0.021 rows=1 loops=1)
    -> Filter: ((PartitionedTable.id >= 6000) and (PartitionedTable.id < 8000))  (cost=0.10 rows=1) (actual time=0.020..0.020 rows=1 loops=1)
        -> Index scan on PartitionedTable using col (reverse)  (cost=0.10 rows=1) (actual time=0.019..0.019 rows=1 loops=1)

Everything looks ok, server found necessary partition (you can see it if you run EXPLAIN without ANALYZE) and read one last row from index on col column.
The second query (which requests the same data but in a bit different way):
EXPLAIN ANALYZE SELECT MAX(col) FROM PartitionedTable WHERE id >= 6000 AND id < 8000;

Execution plan:
-> Aggregate: max(PartitionedTable.col)  (actual time=0.938..0.938 rows=1 loops=1)
    -> Filter: ((PartitionedTable.id >= 6000) and (PartitionedTable.id < 8000))  (cost=400.26 rows=2000) (actual time=0.037..0.799 rows=2000 loops=1)
        -> Index range scan on PartitionedTable using PRIMARY  (cost=400.26 rows=2000) (actual time=0.035..0.570 rows=2000 loops=1)

Now server found the same partition but decided to scan all data inside. I know that my example is artificial (I partitioned table by primary key and used conditions which exactly match partition borders) but this execution plan looks pretty inefficient and strange because server already has an index with properly sorted data and it was able use it in a corresponding way executing the first query.
Link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f27c4b395b8f02a2972cc0ae306fe1cf


texts:
Let's say we have a Person relation, and with that Employee which IS-A Person,  Manager, which ISA Person as well.

What is the correct way to ensure that a Manager isn't an Employee and vice versa ?

I didn't know what to look for when asking the question, there most likely already exists an answer to that question.

Person (PK:personId, name, ...);
Manager(PK:#personId); -- A manager ISA person
Employee(PK:#personId); -- An employee ISA person

INSERT INTO Person (1,'Peter',...);
INSERT INTO Manager (1); -- OK
INSERT INTO Employee(1); -- Nope, error.

  Person cannot be at the same time an employee and a manager.

Maybe with another example.

Shape (shapeId);
Rectangle(shapeId, width, length);
Circle(shapeId, radius);

Circle is a shape, Rectangle is a shape, how do I ensure Rectangle is not a Circle (and vice versa)?

Hence my question, What is the correct way to enforce unique "IS-A" relationship?


texts: Allow user select from table in another user's schema that has policies set

texts:
I have an oracle10g database with 2 users - user A and user B. I want to select data from a table in user A's schema from user B. I have granted user B select privileges on the table in question but I've realized that there is a policy set on the table.

I'm looking for an alternative to granting the "exempt access policy" to user B.


texts: How to customize Deploy feature in VS2010 for SQL CLR projects?

texts:
The default deployment script for SQL CLR objects in VS2010 seems to require one to set the target database to SET TRUSTWORTHY ON and tries to enable vardecimal storage at the database level.

On our landscape we prefer to SET TRUSTWORTHY OFF on our databases.  Also, one cannot enable vardecimal storage on system databases. We have some TVFs that we deploy to master.

It would be nice to be able to have the script be properly generated instead of having to modify it with each build.  For now, I have abandoned the Deploy feature and manually deploy my CLR objects.

How do I customize the autogenerated deployment script?  Is there a way?


texts: Update a table with data from another table: truncated incorrect double value mysql

texts:
I have a Wordpress table in which I want to take the user_email from Table2 and import it to contact_email on Table1 one based off of the user_login. user_login and user_id equal the same value. Nothing I have tried has worked. Any thoughts? I receive truncated incorrect double value mysql when I run the

Table1

  user_id|contact_email |contact_name
 =======================================
 123     |test@test.com |deft
 124     |test3@test.com|deft3

Table2 (User table)

 user_login|user_email     |display_name
 =======================================
 123       |test@test.com  |deft
 124       |test3@test.com |deft3

I have tried:

 update tbl1
 join   tbl2
 on     tbl1.user_id = tbl2.user_login
 set    tbl1.contact_email = tbl2.user_email;

And received truncated incorrect double value mysql

and when I try:

 update tbl1
 join   tbl2
 on     tbl1.user_id = tbl2.user_login
 set    tbl1.contact_email = tbl2.user_email;
 select * from tbl1;

I get 2 queries successfully executed. 0 record(s) were affected. There should be around 2000 records affected, so I know its not right.


texts:
We have a client running SQL Express.

The line of business application appears slow at times and I am trying to diagnose the cause. I suspect it may be because of the Express version.

If the client was to upgrade it would involve significant cost so I need to be sure and have some metrics I can present as part of a business case.

How can I monitor SQL Server to identify if the limitation so the Express version are causing bottlenecks?

MajorVersion    ProductLevel    Edition ProductVersion
SQL2008 R2  SP2 Express Edition (64-bit)    10.50.4042.0


texts:
Query:

declare @X xml = '
<item ID = "0"/>
<item ID = "1"/>
<item/>
<item/>';

select I.X.value('@ID', 'int')
from @X.nodes('/item') as I(X);

Result:

-----------
0
1
NULL
NULL

Execution plan:

The top branch shreds the XML to four rows and the bottom branch fetches the value for the attribute ID.

What strikes me as odd is the number of rows returned from the Stream Aggregate operator. The 2 rows that comes from the Filter is the ID attribute from the first and second item nodes in the XML. The Stream Aggregate returns four rows, one for each input row, effectively turning the Inner Join to an Outer Join.

Is this something that Stream Aggregate does in other circumstances as well or is it just something odd going on when doing XML queries?

I can not see any hints in the XML version of the query plan that this Stream Aggregate should behave any differently than any other Stream Aggregate I have noticed before.