texts: How to retrieve all children of a parent record?

texts:
I have one MySQL table with this columns:

id
father
son
street
city

I want to obtain all descendants of a father recorded, how I can do this?

I tried this:

WITH RECURSIVE filhos AS (
select login, loginpatrocinador from pedido where login<>"" and loginpatrocinador="logindapesquisaaqui"
UNION
select f.login, f.loginpatrocinador from pedido as f, filhos as a where a.login<>"" and   f.loginpatrocinador=a.login
)
SELECT * FROM filhos;

doesn't work...


texts: Open source alternative to SQLMaestro for PostgreSQL

texts:
I'm looking for an open source alternative to SQLMaestro for PostgreSQL because it has visual database designer which is not avaliable in pgAdmin which makes database design so easy.

PS I'm not sure if this is illegal here but I've to ask about it because google didn't help me to find the alternative.


texts:
I want to concatenate columns with a SELECT *. For example:

SELECT * FROM Table1

Result:

Col1 Col2
---- ----
Joe  Smith

I want to do something like SELECT CONCAT(*) FROM Table1

The reason I can't mention the columns by name is that they are being produced by a PIVOT from rows, so there could be any number these columns.

If anyone knows the answer, or knows how to Get the data from any number of rows into a single cell, separated by a comma, please let me know.


texts: SQL Server CPU spikes - possibly non-query related - suggested indicators

texts:
I'm looking for suggestions for indicators I could monitor to discover what is causing these cpu "blunted spikes".

Some known information:

sqlserver.exe is confirmed as the process consuming the CPU (2008 R2 Standard edition).
It's very intermittent in nature.  Can't pin it down to a particular query. Profiler traces don't seem to show any correlation. In fact, it looks as though all queries are being made to wait during the spike, but interestingly there seems to be a correlation with there being a service broker queue with a backlog of messages currently being processed.
Service broker is being used purely for asynchronous intra-database stored procedure execution, with heavy use of CLR functions.

I'm suspecting some sort of background thread or process that is "catching up", but haven't been able to pin it down yet.


texts: Get top SQL Queries that aren't stored procs/functions/triggers

texts:
Afternoon,

I need way to get the query stats for queries that aren't procedures/functions/triggers.

sys.dm_exec_query_stats only appears to contain these, i need to gather similar stats that are in this dmv but for just normal queries submitted by ssms/EF/Web etc.

This is what i used and it returns no results. (not the most efficient but just a quick proof of concept)

SELECT txt.text
   last_execution_time,
   execution_count,
   total_elapsed_time,
   last_elapsed_time,
   min_elapsed_time,
   max_elapsed_time
   FROM sys.dm_exec_query_stats
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS txt
   WHERE
   txt.text NOT LIKE '%CREATE PROC%'
   AND txt.text NOT LIKE '%CREATE FUNC%'
   AND txt.text NOT LIKE '%CREATE TRIGG%'
   AND txt.dbid = DB_ID('$(DB)')

I know there are several queries submitted regularly from client apps that don't use stored procs and take a long time to execute. I would have thought SQL keeps a record of these somewhere.


texts: Difference between GROUP BY B,A and GROUP BY COALESCE(B,A)

texts:
I have been getting myself very confused.

Could somebody kindly explain under what circumstances I would want to use a GROUP BY COALESCE?

My guess is that I would use it if I wanted to conditionally group a set of data by column B (if B was not null) and by column A otherwise.  Does that sound right?


texts:
Good day. This may be a stupid question and you can give me a stupid answer if you need to. I know you can schedule a task to backup a sql database at logon, shutdown, specific time, etc. But What I would like to know is the following:

Is it possible to set a sql database to run at a specific time but if there is a power failure it will run when the pc/server comes back up?

So for example I schedule a full database backup on say Friday evening at 18:00 (an hour after everyone has left), but before the scheduled time there is a power outage and the ups shuts down the server (we have it configured to shutdown the server if our generator is not started in 30 minutes) and once power is back the server automatically starts up but it is after the scheduled backup time, is there a way to get it run at boot.

This isn't mission critical but more of a piece of mind/convenience thing.

TLDR - want to know if a scheduled task will run at boot as server could be powered off due to lack of electricity.


texts: Queries logged in the 'Slow query log', and 'start transaction' vs 'set autocommit = 0'

texts:
Currently long_query_time is set to 5, and that works.

However, many queries are part of a transaction, and the slow query log only keeps the final commit;.

# Time: 2018-08-15T15:18:18.205309Z
# User@Host: bijin[bijin] @ localhost [127.0.0.1]  Id: 3274529
# Query_time: 7.510110  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use interferences_db;
SET timestamp=1534329958;
commit;                      # <=== only 'commit' is logged

Which is not very informative ( while it's correct, as seen from the db server ).

Is there a way to see the queries up to the commit, meaning all queries from the transaction?(or at least some part of it allowing to identify the culprit query)

~~~ edit ~~~

The answer from @AmitPatel had me perform some tests using

set autocommit = 0

instead of

start transaction

to initiate a transaction and, indeed, with autocommit = 0 the slow query log has the details of every slow query, even if they're eventually rollbacked.

So, in order to log slow queries that are part of a transaction, can I safely replace, in the code,

start transaction .... commit / rollback

with

set autocommit = 0 .... commit or rollback and set autocommit = 1 ?

( The final autocommit = 1 is to keep the same behavior in the code, after a transaction is rollbacked or committed )


texts: Missing date handling between subquery and OUTER APPLY

texts:
I have two sub-queries to get visit date and visit type that I combined into one outer apply.
The sub-queries for visit date and visit type give me blanks when there is no visit, which is what I want.
The outer apply gives me blanks for visit type but for date give me 01/01/1900.

I can solve the issue with a where  fldvisitdate <> '19000101'
but I'm wondering why the outer apply is working this way.
here is the code of my two sub-queries.

  (SELECT TOP 1 fldVisitDate FROM tblVisit
inner join tblschedule on tblSchedule.fldScheduleVisitID =       tblvisit.fldvisitid
     WHERE fldVisitPrerepID = tblRunnerPrerepIDs.fldPrerepID
   and tblSchedule.fldScheduleVisitID = tblvisit.fldvisitid
    ORDER BY fldVisitDate)
      AS 'First Visit',

      (SELECT TOP 1
      tblVisitTypes.fldVisitTypeName
     FROM tblVisitTypes
     inner join tblVisit  ON tblVisit.fldVisitTypeID =    tblVisitTypes.fldVisitTypeID
     inner join tblschedule on tblSchedule.fldScheduleVisitID =    tblvisit.fldvisitid
     WHERE fldVisitPrerepIDID = tblRunnerPrerepIDs.fldPrerepID
    and tblSchedule.fldScheduleVisitID = tblpatientvisit.fldvisitid
  ORDER BY fldVisitDate)
   as 'First Visit Type',

        OUTER APPLY (SELECT TOP 1
       tblVisit.fldVisitDate, tblVisit.fldVisitDate,     tblVisitTypes.fldVisitTypeName
      FROM tblVisit
      inner join tblVisitTypes  ON tblVisit.fldVisitTypeID = tblVisitTypes.fldVisitTypeID
      inner join tblSchedule on tblSchedule.fldScheduleVisitID = tblVisit.fldvisitid
     WHERE fldVisitPrerepID = tblRunnerPrereps.fldPrerepID
     and tblSchedule.fldScheduleVisitID = tblVisit.fldvisitid
    ORDER BY fldVisitDate) as FirstVisitData

Of course earlier in the query I request a display of

      FirstVisitData.fldvisitdate as ‘First Visit’
      FirstVisitData.fldVisitTypeName as ‘First Visit Type’


texts:
This is a heap with 400+ Million rows and 16 non clustered indexes, 146 columns. Please do not focus on why I cannot create a clustered index. That is not in my control and not in scope to my question.

SQL Server:

  Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64)   Jan  5 2018 22:11:56   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)
  Database Compatibility Level:SQL Server 2008(100)

When I run following query (anonymized), I get this actual execution plan:

SELECT TOP ? *
FROM Schema1.Object1 Object2
WHERE Object2.Column1 LIKE ?
ORDER BY Object2.Column2 DESC

Same database restored to SQL 2017 CU13 and change compatibility mode to SQL Server 2012 (110) (following is the full version name), and I get this actual execution plan.

  Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64)   Nov 30 2018 12:57:58   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor)

I totally understand that 'ROW GOAL' is in play here. You cannot see it in the first plan as it was not exposed in the query plan before. To be sure I can run the SQL Server 2008 version with Trace Flag 4138 and SQL Server 2012 version with DISABLE_OPTIMIZER_ROWGOAL hint. Both cases I get significant performance gain. Gain happens because optimizer pick the right index and does not choose a nested loop join.

Question:

While both compatibility level is prior to SQL Server 2014 version why EstimatedRowsWithoutRowGoal (I understand you cannot see it in 2008) is different?
Why Index Scan qualify 1.1 Million rows in SQL 2008 vs 8.7 Million rows in SQL 2012? What heuristics changed (even I am not using new optimizer launched with Compatibility 120)?

I read this article by Paul White and all 8 links given in the article.
Also read More Showplan enhancements – Row Goal by Pedro Lopes


texts:
I enter the following command into the SQL Server Management Studio

insert into testtable ([product_name], [price], [expire_date], [expire_time])
  values ('Teapot', 10.00, 23/12/2012, '12:35:00')

It yields this error:

  Operand type clash: int is incompatible with date

These are the columns:

  Column name   |   datatype
----------------+---------------
product_name    | varchar(30)
price           | money
expire_date     | date
expire_time     | time(7)


texts: Database not able to resume data movement

texts:
For some unknown reason, a few of my databases went into a Not Synchronizing / Recovery Pending state on my secondary node in my AG. I was able to resume data movement on all of them except one.

I ran the command ALTER DATABASE RedGateMonitor SET HADR RESUME like I did for the other databases but the alter never finished. I let it run for a long time. I believe there is something blocking it.

I looked at the Activity Monitor and saw the ALTER statement running and the wait_type is LCK_M_U.

Since it never completed, I killed the process.

Even now, after the alter was killed, I run a query to show any waits and I see this:

(No column name)    Hostname    session_id  wait_duration_ms    wait_type   blocking_session_id host_name   program_name
WAITING             DB02P       104         153                 WAITFOR  NULL   REDGATE01M  SQL Monitor - Monitoring

I have since moved this database to another server and the RedGate monitor service is connected to the new location, so this copy is no longer needed but I need to get it syncing again so I can remove it from the AG and then drop it.

Any ideas on what I can look at to see what's blocking the resume data movement? And how to remove the block if it is in fact a block.

UPDATE

I now get an error saying it's not part of the AG when I try to resume data movement. So I tried to remove it from the AG but it shows it's blocked.

spid    blocked waittime_ms waittime_sec    db_name open_tran   waitresource    status  level   hostname    program_name    sql_text

158 0   3120    3   master  0                                                                                background                      0                                                                                NULL

117 158 133969  133 master  0   DB: 24:0 [HADR_STATE_MANAGEMENT]                                                                                suspended                       1   DB02P                                                                                Microsoft SQL Server Management Studio                                                                                ALTER DATABASE [RedGateMonitor] SET HADR OFF;

Sorry for the bad formatting.

UPDATE2

The database on the secondary node is in RECOVERY_PENDING state with the reason as SUSPEND_FROM_REDO. How do I get it out of this state so I can drop this database? It currently won't let me drop it due to a system lock on it.

UPDATE3

I spoke with Microsoft Support and they had me stop the SQL service in SQL Configuration Manager which wouldn't stop so we have to end the SQL server service process in Task Manager. Not a good practice but the service would not stop. Then we renamed the log file of the database to a dummy name so that when SQL starts, the database would be in Recovery. We started SQL, the database was in recovery and we were then able to drop the database on the second node. So it seems like there was some type of system level lock on the database and only a restart of the service resolved the issue.


texts: MySQL: Return JSON from a standard SQL Query

texts:
I have read about JSON objects and the JSON object type.  I only want to do a select and it return JSON.  I do not necessarily want to store a JSON object.  Serialization per se it not my question.  The columns are regular Varchar, Int, etc. columns, no JSON Objects, "normal" database rows.

Can I do a regular old SELECT and return JSON for MySQL?

Isn't this what FOR JSON in SQL SERVER and rows_for_json do in PostgreSQL?

They seemed ahead in this are but I didn't want to fool myself.

I found this question from 2016:
https://stackoverflow.com/questions/35324795/mysql-5-7-return-row-as-json-using-new-json-features


texts: Database for storing frequency of access token usage

texts:
I would like to create a database to store how often access tokens in my system are being used.

I was thinking of a data model like so -

Access Token
from (basically I will store this hourly, so this will be the timestamp of teh start of the hour)
Number of times used

What would be the ideal database for this? I am conflicted with several choices.

Some more details to help out -

We get around 5k requests per minute, so the DB will have to be quick enough to update that.
We will not often be querying the database, probably only be looking for daily summaries and month end aggregations for accounting and auditing purposes.
We would prefer this to be on AWS as a service, but we can also spin up instances on our own.


texts: MySQL DB Migration from on-premises to AWS through replication

texts:
I am having a production MySQL server with set of 20 databases in on-premises server. Now I require to migrate few databases from production server to AWS RDS without disturbing other databases in one-by-one.

We can perform this activity using AWS DMS. Instead, I am looking at tranditional MySQL replication for setting up replication between existing production server to AWS RDS.

In RDS, as we are not having access to my.cnf, I am not able to set replicate-do-db configuration. Is there any other workaround to setup replication from on-premises to RDS for few databases.


texts: SQL Server query on server taking longer than laptop

texts:
Our program uses an SQL server database as its back-end (SQL server 2008-r2 - 2014). The program is OK for performance but the Database seems to slow down a bit too much.

As part of the nightly process we need to run quite a lot of queries which currently is finishing too late. (Start at 12:00 midnight end at 9:00 am. People start work then and need the database) This is OK for now but we are still setting up the database and more batch jobs need to be setup.

For example with the same database on my laptop and the server one of the queries that we need to improve takes 2 minutes on my work laptop and 3 hours on our clients server. The clients server is new and has been built to our specs that we gave them(Specs below).(Server is dedicated to our SQL instance.)

So the server is taking 9000% times longer to run the query than my work laptop.
This is not just for the one query or one client server. This is happening with most of our client servers and their queries.

I understand it should be faster on my laptop but I'm thinking not by that much.
So my question is: What on our clients server environments can be making that type of difference? and What can I do about it?

Specs

OS Version: Windows Enterprise Ed

Memory 96 GB

Processor: 2 * 8 Core HyperThreaded CPU's

Controller 1

Disk subsystem 1: Raid 1 for OS - 2 * 300GB 10K HDD

Disk subsystem 2: Raid 10 for SQL Server Data - 8 * 300GB 15K HDD

Disk subsystem 3: Raid 10 for SQL Server TempDB - 4 * Enterprise grade SDD 256GB

Controller 2

Disk subsystem 4: Raid 10 for SQL Server Logs 8 * 146GB 15K HDD

Disk subsystem 5: Raid 10 for SQL Server Non-clustered Indices - 4 * Enterprise grade SDD 512GB


texts: I have an INSTEAD OF trigger, but PostgreSQL still complains while I insert into view

texts:
I have created a view and an INSTEAD OF trigger for inserting/ updating/ deleting from that view. Now I have tried to insert some data to the view, and PostgreSQL returns following error (translation from Czech: "chyba" = "error", "Stav SQL" = "SQL state"):

ERROR:  cannot insert into view "ukaz_lok"
DETAIL:  Views that return columns that are not columns of their base relation are not automatically updatable.
HINT:  To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
********** Chyba **********

ERROR: cannot insert into view "ukaz_lok"
Stav SQL: 55000

This happened before with some other INSTEAD OF triggers - this bug lasted just for some time (few hours? I don't remember exactly) and then PostgreSQL found the trigger. I tried various things (change the trigger, delete all triggers and views and creating them anew etc.), but I'm not sure what worked, just that something worked. So how exactly to make the PostgreSQL recognize the trigger?

I have PostgreSQL 9.3.4 and pgAdmin III 1.18.1 on Windows XP 32 bit. Just in case it was caused by something in the code, here it is:

CREATE TABLE lokalita
(
  kod_lok text NOT NULL,
  nazev text NOT NULL,
  katastr text NOT NULL,
  presnost integer NOT NULL,
  stred geometry(Point,4326) NOT NULL,
  rozsah geometry(Polygon,4326),
  CONSTRAINT pk_lok PRIMARY KEY (kod_lok),
  CONSTRAINT fk_pro_pr FOREIGN KEY (presnost)
      REFERENCES pro_presnost (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

CREATE OR REPLACE VIEW ukaz_lok AS
  SELECT
    l.kod_lok AS _kod,
    l.nazev AS _nazev,
    l.katastr AS _katastr,
    l.presnost AS _presnost,
    50.0 AS _sirka,
    14.0 AS _delka
  FROM lokalita l;

CREATE OR REPLACE FUNCTION osetri_lok() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO lokalita (kod_lok, nazev, katastr, presnost, stred)
    VALUES(
      NEW._kod,
      NEW._nazev,
      NEW._katastr,
      NEW._presnost,
      ST_GeomFromText('POINT('|| NEW.delka ||' '|| NEW.sirka ||')', 4326)
    );
  ELSIF TG_OP = 'UPDATE' THEN
    UPDATE lokalita
    SET
      nazev = NEW._nazev,
      katastr = NEW._katastr,
      presnost = NEW._presnost,
      stred = ST_GeomFromText('POINT('|| NEW.delka ||' '|| NEW.sirka ||')', 4326)
    WHERE lod_lok = OLD._kod;
  ELSIF TG_OP = 'DELETE' THEN
    DELETE FROM lokalita WHERE kod_lok = OLD._kod;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER osetri_lok
INSTEAD OF DELETE ON ukaz_lok
FOR EACH ROW
  EXECUTE PROCEDURE osetri_lok();

INSERT INTO ukaz_lok (_kod, _nazev, _katastr, _presnost, _delka, _sirka)
  VALUES(
  'Kote2',
  'Kotěhůlky 2',
  'Kotěhůlky',
  2,
  14.060316,
  50.432044
);


texts: How to reduce LOG file after switching from full recovery mode to simple mode?

texts:
I have a database with full recovery model and without TLog backup.

The log file have grown to 160GB and I found out that it needs TLog to be periodically backed up or the recovery model switched to simple. Now that I have switched to simple recovery model, the .ldf file is still of the same size.

How can I reduce it?


texts: Any risks in updating the system time on a mysql 5.5 server?

texts:
We just discovered one of our mysql systems has not been running ntp, with the result that the time has drifted a bit.

If I enable ntp, and synchronize the time while the system is running are there any possible problems?  The master is currently about 4 minutes slow, and a slave is about 3:45 behind.


texts:
I have table named ITEMS where there should be 2 auto increment columns.

So the table structure would be:

ID   |   ITEM_ID
---------------------------------
1    |   ITEM201521080225-001
2    |   ITEM201521080301-002
3    |   ITEM201521080345-003

The column.ITEM_ID structure is ITEMYearDayMonthHourMinute-Increment Value

I managed to Generate the ITEMYearDayMonthHourMinute value thru POST(PHP POST/ PHP Generated).

I am using MySQL and PHPMyAdmin at the moment, and I'm seeing errors if I set column.ITEM_ID to AUTO_INCREMENT

How can I make column.ITEM_ID to AUTO_INCREMENT? Is it Possible?


texts:
I am polling CDC tables to synchronize data between SQL Server and MongoDB. The approach I have taken is below:

Enabled cdc for dbo.table1 and dbo.table2 which creates cdc.dbo_table1_CT and cdc.dbo.table2_CT
Created an insert trigger for cdc.dbo_table1_CT and cdc.dbo.table2_CT. Then the trigger will writes the changed table's name and schema to another table, say tbl_consolidated_tracks
Then I have a service which polls tbl_consolidated_tracks and then from that the service reads the changes and syncs to mongodb

What I am trying to do here is to avoid triggers and the usage of tbl_consolidated_tracks.

Can I directly poll the changed tables from any cdc procedure or table?


texts: How to take physical backup of MYSQL database

texts: I am trying to find out some tool which can take physical backup of MYSQL database. Like mysqlbackup but it is not open source. There is mysqldump but i want to take physical backup. Can someone please suggest me some tool to take backup of MYSQL db on windows. And i am using Innodb as my database engine.


texts:
I have about more than 250 Firebird local databases and several server databases. The way they are connected is such:

A block of almost 50 local databases, 1 on each PC, connect to 1 server database. This repeats for other blocks of almost 50 local databases for other server databases.

I would like to unite all the local databases with the server databases and migrate all this to Oracle.

What way do you recommend I do it? Are there solutions that will help me do it quickly and easily?

Edit:

Firebird version 2.5
Oracle 12.2 (Edited, we do have an 12.2 Oracle license)

Local databases have different data but equal schemes.
Server databases have different data but equal schemes.

The data should be migrated last and should be done quickly (within a day).


texts:
I have two columns in a SQL Query ( Estimate and Billed ) which contain un-formatted numbers.

I am trying to write a CASE WHEN query for when the BILLED column is between 50% and 70% of the BILLED column THEN '1' ELSE '0'

Then the same for 70% to 90% , 90% to 100% and then greater than 110% ( so multiple CASE WHEN ).

I am just not familiar with the arithmetic needed or if this is the best way to achieve this...

Same Query, with the CASE WHEN in plain English to help explain:

SELECT

CaseNumber,
EstimateAmt,
BilledAmt,

CASE WHEN BilledAmt IS BETWEEN 50% and 70% of EstimateAmt THEN '1' ELSE '0' end as 'IsBetween50And70',

CASE WHEN BilledAmt IS BETWEEN 70% and 90% of EstimateAmt THEN '1' ELSE '0' end as 'IsBetween70And90',

CASE WHEN BilledAmt IS BETWEEN 90% and 100% of EstimateAmt THEN '1' ELSE '0' end as 'IsBetween90And100',

CASE WHEN BilledAmt IS > 110% of EstimateAmt THEN '1' ELSE '0' end as 'IsGreaterThan110'

from CaseList

Thank you


texts:
How do you get the Backup History for a PostgreSQL database from within the database?
for example:
in SQL Server you look into msdb.dbo.backupset
in Oracle you check v$rman_output
is there a similar way here
Thanks


texts: How do I find the cumulative distance between geometry points in SQL Server 2014?

texts:
I have a database designed to be a proof of concept for delivery of products from a central depot to customers distributed at random locations within ten miles of the depot. The depot location is expressed in absolute coordinates (0,0) and customer locations are stored in the database in two columns with Coord_X and Coord_Y. The code I have at present to determine which deliveries are due on a particular day is as follows:

SELECT t_Orders.Order_ID, t_Orders.Del_Sess, t_Customers.Contact_Name, t_Addresses.Cust_ID, t_Addresses.Town, t_Addresses.County, t_Addresses.Coord_Y, t_Addresses.Coord_X, t_Customers.Comp_Name, t_Orders.Cust_ID,
t_Orders.Del_Date
FROM t_Orders
JOIN t_Customers
ON t_Orders.Cust_ID=t_Customers.Cust_ID AND t_Orders.Del_Sess=1 AND t_Orders.Del_Date = '2016-05-21'
JOIN t_Addresses
ON t_Orders.Cust_ID=t_Addresses.Cust_ID AND (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X <= 0 AND SQUARE(t_Addresses.Coord_X) < SQUARE(t_Addresses.Coord_Y))
OR (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X >= 0 AND (t_Addresses.Coord_X) < (t_Addresses.Coord_Y))
ORDER BY SQUARE (t_Addresses.Coord_X) + SQUARE (t_Addresses.Coord_Y) DESC

This returns a list of all deliveries, with the furthest away first. I'd like to be able to take the output of this query and run queries on it (I looked into temporary tables and views, but can't seem to get this into one) to first remove any duplicates (e.g. multiple deliveries to one customer on a particular day as each order is treated as one delivery) and then run a query that says:

Starting at the furthest away, calculate distance to second furthest away, then from second furthest away, calculate distance to third furthest and so on until back at HQ.

I also have a geometry column for each customer location if that helps - e.g. is there a very easy way to do this using geometry, declaring HQ as a point first and letting SQL server work out the cumulative shortest distance back?

It's travelling salesman in a way, but a very simple version of it. I'm not looking for the shortest route per se. I'm looking for:

Order deliveries by distance from HQ
Draw line between furthest out delivery and HQ, which moves to through each point of delivery in order as determined earlier at 1
Measure length of that line.

This is the SQL I have at present to bring back the delivery locations and coordinates:

SELECT t_Addresses.Coord_X, t_Addresses.Coord_Y, t_Addresses.Cust_ID
FROM t_Addresses
WHERE t_Addresses.Cust_ID IN
(SELECT DISTINCT t_Orders.Cust_ID
FROM t_Orders
WHERE t_Orders.Del_Date='2016-05-21' AND t_Orders.Del_Sess=1)
AND (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X <= 0 AND SQUARE(t_Addresses.Coord_X) < SQUARE(t_Addresses.Coord_Y))
OR (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X >= 0 AND (t_Addresses.Coord_X) < (t_Addresses.Coord_Y))
ORDER BY SQUARE (t_Addresses.Coord_X) + SQUARE (t_Addresses.Coord_Y) DESC


texts: Executing query is too slow when accessing SQL Server located in an other country

texts:
I'am doing an investigation about a slow running query (see below info) when accessing our production SQL Server located in an other country. Now I'm looking for tweaks on how to solve this and make it fast.

See below information:

Database Transaction Table Records: 17,000 +
SQL Server(2005), Web Server, Report Server (where reports are on our ASP.NET Web Application) location: Illinois, USA (no jobs or any linked server on the Database)
Query executed: select * from TransactionTable

Test results (all locations have only one domain):

|  Location       | Time Elapse  | Executed Using
   Illinois         00:00:00       SQL Management Studio,Web App,RS(Prod Server,local)
   Brazil           00:2:21 +      SQL Management Studio,Web App,RS
   Philippines      00:2:21 +      SQL Management Studio,Web App,RS
   Others           00:2:21 +      SQL Management Studio,Web App,RS

This is what I did: restart production SQL Server, stop Web and Report Server then I tried to query the SQL Server on different locations with the same result.

I found that the problem is not the query but it is caused by network

Anyone of you guys encountered this? What have you done? I need some recommendation.


texts: Source for extended-event category and channel information?

texts: The SSMS GUI for Extended Events lets you filter events by category ("errors", "execution", etc) and channel ("Analytic", "Operational", etc).  How can I get the category and channel mapping for an event programmatically?


texts:
I created a new database by using the command line binary on Suse.
After activating headers by using .headers ON it still looks messy when getting table information with PRAGMA table_info(TABLE1);:

cid|name|type|notnull|dflt_value|pk
0|COLUMN1|REAL|1||0

How can I achieve a cleaner look like this:

sqlite> pragma table_info(member);
cid      name          type     notnull  dflt_value  pk
-------  ------------  -------  -------  ----------  -------
0        account_id    text     1                    0
1        account_name  text     1                    0


texts:
I have following query which has ON clause on different column, Where clause on different column and Selection on different columns so my question is what should be Order of Columns in Index as according to my knowledge order of columns matter.

 SELECT     AA.[Column7],
            AA.[Column2],
            AA.[Column3],
            ISNULL(AA.[Column23], 0) AS [Column23],
            BB.[Column2] + '. ' + BB.[Column3] + ', ' + BB.[Column4] AS UserName,
            AA.[Column1],
            AA.[Column4],
            AA.[Column5],
            AA.[Column6],
            AA.[Column8],
            ISNULL(AA.[Column9], 0) [Column9],
            ISNULL(AA.[Column10], 0) [Column10],
            ISNULL(AA.[Column11], 0) [Column11],
            ISNULL(AA.[Column12], 0) [Column12],
            AA.[Column13],
            AA.[Column14],
            AA.[Column15],
            ISNULL(AA.[Column19], '') [Column19],
            CC.[Column1]
    FROM    Table1 AA
            INNER JOIN Table2 BB ON AA.[Column6] = BB.[Column1]
            LEFT JOIN Table3 CC ON AA.[Column22] = CC.[Column1]
    WHERE    AA.[Column5] = @Vailable1
    ORDER BY AA.[Column2] DESC,
             AA.[Column1] DESC

So far I have tried below strategy but I am not sure whether it’s according to best practice or not.

CREATE NONCLUSTERED INDEX [IX_IndexName] ON [dbo].[Table1]
(
    [Column5] ASC,
    [Column2] DESC,
    [Column1] DESC,
    [Column22] ASC
)
INCLUDE (
            [Column3],
            [Column4],
            [Column6],
            [Column7],
            [Column9],
            [Column10],
            [Column11],
            [Column12],
            [Column13],
            [Column14],
            [Column19],
            [Column23],
            [Column15],
            [Column8]
       )

EDIT:
During research on order of columns in index I found a link where there is a statement

  Very often you'll find that a better index is on (MostSelective)
  include (SecondMost, Least) or on (MostSelective, SecondMost) include
  (Least)

How do I judge what are MostSelective columns, SecondMost and Least? Is this depending on data?


texts: How do I write this cross Join in MySQL?

texts:
It should create a set of columns (ControlRange, SensorBias, SensorError, VDCurve, INSHalfCurve, INSSensCurve, ExogDEXCurve, InitGlucose) and all permutations of their values. I want the order given to be the order of permutation (permute through all values of ControlRange before incrementing SensorBias). I get syntax errors when running this in MySQL, which worked fine in SQL Server.

USE IMTsimulations
;WITH Five(num) AS
(
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
), Four(num) AS
(
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
), Three(num) AS
(
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2
), Two(num) AS
(
  SELECT 5 UNION ALL SELECT 6
)
-- ##INSERT INTO [dbo].[Parameters]
     -- (ControlRange, InitGlucose, ExogDEXCurve, INSSensCurve, INSHalfCurve, VDCurve, SensorError, SensorBias)
     -- ##(ControlRange, SensorBias, SensorError, VDCurve, INSHalfCurve, INSSensCurve, ExogDEXCurve, InitGlucose)
     -- USE the SELECT to enforce incrementation order

SELECT cr.[num] AS [ControlRange],
       sb.[num] AS [SensorBias],
       se.[num] AS [SensorError],
       vd.[num] AS [VDCurve],
       i_t.[num] AS [INSHalfCurve],
       i_s.[num] AS [INSSensCurve],
       exdex.[num] AS [ExogDEXCurve],
       initg.[num] AS [InitGlucose]
FROM Two cr
CROSS JOIN Three initg
CROSS JOIN Five exdex
CROSS JOIN Five i_s
CROSS JOIN Four i_t
CROSS JOIN Four vd
CROSS JOIN Three se
CROSS JOIN Five sb
ORDER BY ControlRange, SensorBias, SensorError, VDCurve, INSHalfCurve, INSSensCurve, ExogDEXCurve, InitGlucose


texts: Get total size for all Postgres or Greenplum tables including partitions

texts:
Is there an easy way to get the total size of all tables including partitions? I've written this query for Greenplum, but it's a bit round-a-bout:

with part_sz as (
  select ts.sotdschemaname sch
       , coalesce(ps.sopaidparenttablename,ts.sotdtablename) tbl
       , coalesce(ps.sopaidpartitiontablesize,ts.sotdsize) part_size
  from gp_toolkit.gp_size_of_table_disk ts
  left join gp_toolkit.gp_size_of_partition_and_indexes_disk ps
  on ts.sotdschemaname = ps.sopaidpartitionschemaname
  and ts.sotdtablename = ps.sopaidpartitiontablename
)
, tbl_sz as (
  select sch, tbl, sum(part_size) total_size
  from part_sz
  group by sch, tbl
)
select sch, tbl, total_size
from tbl_sz
order by total_size desc

Greenplum is built on Postgres 8.2, so something using that feature set would be ideal, but feel free to post anything using newer Postgres versions for the benefit of other readers.


texts: How to change owner of a table within a PostgreSQL Database on Azure?

texts:
I attempting to restore a database into a PostgreSQL instance on Azure.

I am issuing these statements as postgres and attempting to change owner to another role that was a superuser.

My restore errors on this line (actual names changed to protect the innocent):

ALTER TABLE schema_m.table_a OWNER TO role_a;

SQL Error [42501]: ERROR: must be member of role "role_a"
I've tried:  GRANT postgres TO role_a GRANTED BY postgres;

I believe the issue is related to the postgres user not being a superuser on Azure PostgreSQL, but maybe I'm wrong.

I've also tried granted all privs to both:

 GRANT ALL PRIVILEGES ON SCHEMA  schema_m TO role_a;
 GRANT ALL PRIVILEGES ON SCHEMA  schema_m  TO postgres;

But I still receive the error. Am I missing something obvious?

PostgreSQL 9.5.14, compiled by Visual C++ build 1800, 64-bit


texts:
I restored a database from SQL Server 2014 Enterprise to SQL Server 2017 Standard.

The restore went through fine without any issues/errors.

I then run DBCC CHECKDB on the database and I get the following messages related to the service broker. Any help in understanding this is appreciated.

  Msg 8997, Level 16, State 1, Line 1 Service Broker Msg 33305, State 1:
  The multicast message body row with reference 28671 should have
  reference count value of 0.


texts: On what basis DMA identifies columns for Dynamic Data Masking?

texts:
Can anyone help me explain on what basis 'Data Migration Assessment' features recommendation suggests for Dynamic Data Masking and displays certain objects/tables columns for masking. On what basis DMA identifies those columns for vulnerabilities.

Is there any set algorithm for identification or is it simply based on the name of the column?

Please comment in case of any more details or clarification required for the question.


texts: Fetch nested results as JSON PostgreSQL 9.2

texts:
Consider the following tables

Device
--------
id
name
type
--------

components
--------
id INT
type VARCHAR
--------

Manufacturers
-------------
id INT
name VARCHAR
country VARCHAR
-------------

Device_components
-----------------
deviceid REFERENCES Devices(id)
componentid REFERENCES Components(id)
-----------------

Component_Manufacturers
-----------------------
componentid REFERENCES Components(id)
manufacturerid REFERENCES Manufacturers(id)
-----------------------

I want to query the database to return something like this:

{
 "id": 1,
  "name": "phone",
  "components": [
    {
      "id": 1,
      "type": "screen",
      "manufacturers": [
       {
         "id": 1,
          "name": "a",
          "country": "Germany"
        }
      ]
   },
   {
     "id": 2,
      "type": "keyboard",
      "manufacturers": [
        {
         "id": 1,
          "name": "a",
          "country": "UK"
        }
     ]
   }
  ]
}

So far I'm selecting from each table individually and then assemble the JSON object in my application.

Here's some sample data:

CREATE TABLE IF NOT EXISTS Devices
(
    ID SERIAL PRIMARY KEY NOT NULL,
    NAME VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS Components
(
    ID SERIAL PRIMARY KEY NOT NULL,
    NAME VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS Manufacturers
(
    ID SERIAL PRIMARY KEY NOT NULL,
    NAME VARCHAR(30) UNIQUE NOT NULL,
    COUNTRY VARCHAR(40)
);

CREATE TABLE IF NOT EXISTS Device_components
(
DeviceID INT REFERENCES Devices(ID),
ComponentID INT REFERENCES Components(ID)
);

CREATE TABLE IF NOT EXISTS Component_manufacturers
(
ComponentID INT REFERENCES Components(ID),
ManufacturerID INT REFERENCES Manufacturers(ID)
);

INSERT INTO Devices (NAME) VALUES ('phone');
INSERT INTO Devices (NAME) VALUES ('tablet');
INSERT INTO Devices (NAME) VALUES ('pc');

INSERT INTO Components (NAME) VALUES ('mouse');
INSERT INTO Components (NAME) VALUES ('camera');
INSERT INTO Components (NAME) VALUES ('screen');

INSERT INTO Manufacturers (NAME,Country) VALUES ('foo','france');
INSERT INTO Manufacturers (NAME,Country) VALUES ('bar','spain');
INSERT INTO Manufacturers (NAME,Country) VALUES ('baz','germany');

INSERT INTO Device_components VALUES (1,2);
INSERT INTO Device_components VALUES (1,3);
INSERT INTO Device_components VALUES (2,2);
INSERT INTO Device_components VALUES (2,3);
INSERT INTO Device_components VALUES (3,1);
INSERT INTO Device_components VALUES (3,2);
INSERT INTO Device_components VALUES (3,3);

INSERT INTO Component_manufacturers VALUES (1,1);
INSERT INTO Component_manufacturers VALUES (1,2);
INSERT INTO Component_manufacturers VALUES (1,3);
INSERT INTO Component_manufacturers VALUES (2,2);
INSERT INTO Component_manufacturers VALUES (3,3);


texts: Practical Aspects of Making Changes to Databases

texts:
I am setting up my first database and have gone through the standard process of studying the business logic, figuring out the entities, associated fields and dependencies.

This is the theoretical/classroom side, which is useful, but maybe does not give the full picture. I'm trying to get advice from those with practical experience in making changes to existing databases, i.e., adding/deleting fields to a table and adding/updating tables to the database.

How is this done; how are dependency issues managed?
Do we use auxiliary programs like Apex, or is this done in other ways?
How hard is it in actuality to change a database?

EDIT: My apologies for not making this post more specific; I am caught in a sort-of catch-22: Since I don't know enough, I cannot ask good, specific questions, and since I can't ask good questions, I can't learn enough.


texts: Oracle DATE column - How to convert to UTC

texts:
I have an oracle 10g table with a DATE column type.  I need to compare it in a where clause to a value generated by a script that looks like this:  TIMESTAMP '2016-05-12 08:00:00.258000 +00:00'.  I cannot control the generated TIMESTAMP part, and when I do:

RECORDEDTIME >= TIMESTAMP '2016-05-12 08:00:00.258000 +00:00' it uses the localtime of the RECORDEDTIME which means I am 4 hours off.  What function do I call on RECORDEDTIME to get it in UTC matching the other value in the WHERE clause (i.e. it needs to subtract 4 hours as I'm in EST)


texts:
I want to update the table car_check field fencing.(total 40 rows).I also use postgis function.
I think it's a little difficult.Any suggestions?

The code is the first carid example.

UPDATE car_check
set fencing=ST_Contains
from (
SELECT ST_Contains(ST_AsText('01030000..'),
'POINT(23.912784 120.99178)')
)as foo
where carid='AD-5487' and cargroupid='1'


texts:
I cannot find documentation anywhere on what would trigger this recompilation reason. We are investigating a sudden query performance drop and the only thing that I can think of is that the plan got recompiled for the parameterized query when executed against a small dataset which resulted in messing with row estimates. We noticed that when this process was running (after it started taking hours instead of seconds) it was hitting tempdb pretty hard. Statistics on consumed tables were not changed and the only other reason that makes sense in the list of recompilation reasons is #12 "Parameterized plan flushed".

The process in question was calling a view and filtering on a single INT column. This was done through Entity Framework.  There is only 1 Entity Key on the class and it is the PK of the main table in the view.   All records are unique.

I am curious if anyone can point me to any documentation out there that explains why a plan might be recompiled due to "Parameterized plan flushed".


texts: index creation - Slow Query - PostgreSQL 9.2

texts:
I've got the following query:

SELECT
* FROM ( SELECT split_part(full_path, '/', 4)::INT AS account_id,
       split_part(full_path, '/', 6)::INT AS note_id,
       split_part(full_path, '/', 9)::TEXT AS variation,
       st_size,
       segment_index,
       reverse(split_part(reverse(full_path), '/', 1)) as file_name,
       s.segment_data,
       i.st_ino,
       full_path,
       (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
FROM gorfs.inodes i
JOIN gorfs.inode_segments s
  ON i.st_ino = s.st_ino_target
WHERE i.checksum_md5 IS NOT NULL
  AND s.full_path ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+'
  AND i.st_size > 0) as test WHERE account_id = 12115;

Explain analyze for the query above: http://explain.depesz.com/s/rHOU

It seems the query is not using the Index I've created: ix_full_path

Table inodes_segments:

CREATE TABLE gorfs.inode_segments
(
  st_ino "gorfs"."ino_t" NOT NULL,
  segment_index "gorfs"."pathname_component" NOT NULL,
  st_ino_target "gorfs"."ino_t",
  full_path "gorfs"."absolute_pathname",
  segment_data "bytea",
  CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
  CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
  CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <> "st_ino_target"::bigint OR "st_ino"::bigint = 2)
)

Table inodes:

CREATE TABLE gorfs.inodes
(
  st_dev "gorfs"."dev_t" DEFAULT NULL::bigint,
  st_ino "gorfs"."ino_t" NOT NULL DEFAULT "nextval"('"gorfs"."inodes_st_ino_idseq"'::"regclass"),
  st_mode "gorfs"."mode_t" NOT NULL,
  st_nlink "gorfs"."nlink_t" NOT NULL,
  st_uid "gorfs"."uid_t" NOT NULL,
  st_gid "gorfs"."gid_t" NOT NULL,
  st_rdev "gorfs"."dev_t",
  st_size "gorfs"."off_t",
  st_blksize "gorfs"."blksize_t",
  st_blocks "gorfs"."blkcnt_t",
  st_atime "gorfs"."time_t" NOT NULL,
  st_mtime "gorfs"."time_t" NOT NULL,
  st_ctime "gorfs"."time_t" NOT NULL,
  checksum_md5 "md5_hash",
  media_subtype_id integer,
  external_size "gorfs"."off_t",
  CONSTRAINT pk_inodes PRIMARY KEY ("st_ino"),
  CONSTRAINT fk_media_subtype_must_exist FOREIGN KEY (media_subtype_id)
      REFERENCES public.media_subtypes (media_subtype_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT cc_mount_devices_not_supported CHECK ("st_dev" IS NULL)
)

I've already created the following indexes:

CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (("full_path")::"text");
CREATE INDEX CONCURRENTLY ix_checksum_st_size ON gorfs.inodes USING btree ("checksum_md5","st_size") WHERE checksum_md5 IS NOT NULL;

But it didn't work.

Question:

What can I do to improve the Query?


texts: I am working with an Azure SQL Server database in SSMS 2016. I have Intellisense enabled in Tools -> Options -> Text Editor -> Transact-SQL -> Intellisense, however I have never had Intellisense working on this computer. Any advice on how to resolve this would be appreciated.


texts: How to avoid 2013 error when executing ALTER TABLE

texts:
I have a huge table (2.8 GB) and I need to add a column. After I run this query I experience a lost connection error (Error code: 2013. Lost connection to MySQL server during query):

ALTER TABLE `huge_one`
ADD COLUMN `extra_column` INT(10) UNSIGNED NOT NULL AFTER `existing_column`;

It seems the problem is related to memory shortage though I have these settings in the config file:

innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Is that problem really related to innodb_log_file_size setting and is it possible to handle it without increasing innodb_log_file_size?


texts: In Postgres, how to get a list of current defined savepoint?

texts:
I'm using postgres SAVEPOINT which create a new savepoint within the current transaction, and would like to display the list of currently defined savepoint in a connection.

To be more precise: I'd like to check which name would NOT trigger the "no such savepoint" error in a connection.


texts: Postgresql: very slow results with "group by"

texts:
I am trying fix some severe performance issues on codechecker. Issue reported here:
https://github.com/Ericsson/codechecker/issues/1399#issuecomment-368862492

The table is the following:

                                       Table "public.reports"
      Column      |            Type             |                      Modifiers
------------------+-----------------------------+------------------------------------------------------
 id               | integer                     | not null default nextval('reports_id_seq'::regclass)
 file_id          | integer                     |
 run_id           | integer                     |
 bug_id           | character varying           |
 checker_id       | character varying           |
 checker_cat      | character varying           |
 bug_type         | character varying           |
 severity         | integer                     |
 line             | integer                     |
 column           | integer                     |
 checker_message  | character varying           |
 detection_status | detection_status            |
 detected_at      | timestamp without time zone | not null
 fixed_at         | timestamp without time zone |
Indexes:
    "pk_reports" PRIMARY KEY, btree (id)
    "ix_reports_bug_id" btree (bug_id)
    "ix_reports_file_id" btree (file_id)
    "ix_reports_run_id" btree (run_id)
Foreign-key constraints:
    "fk_reports_file_id_files" FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
    "fk_reports_run_id_runs" FOREIGN KEY (run_id) REFERENCES runs(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "bug_path_events" CONSTRAINT "fk_bug_path_events_report_id_reports" FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
    TABLE "bug_report_points" CONSTRAINT "fk_bug_report_points_report_id_reports" FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED

You will notice the lack of indexes.

The following query takes a while to run (26 seconds) with 2 469 648 elements in the table.

SELECT reports.run_id AS reports_run_id,
  reports.detection_status AS reports_detection_status,
  count(DISTINCT reports.bug_id) AS count_1
FROM reports
GROUP BY reports.run_id, reports.detection_status

with

 GroupAggregate  (cost=566298.15..590994.88 rows=25 width=41) (actual time=6782.100..26183.127 rows=28 loops=1)
   Group Key: run_id, detection_status
   ->  Sort  (cost=566298.15..572472.27 rows=2469648 width=41) (actual time=6768.582..9561.902 rows=2469648 loops=1)
         Sort Key: run_id, detection_status
         Sort Method: external merge  Disk: 123104kB
         ->  Seq Scan on reports  (cost=0.00..152130.48 rows=2469648 width=41) (actual time=0.010..3248.899 rows=2469648 loops=1)
 Planning time: 0.066 ms
 Execution time: 26224.844 ms
(8 rows)

Any idea how to improve the query or a missing index? Thanks!


texts: How to validate DB Users/logins across Availability Group by script

texts:
How to validate DB users in AG Groups across nodes by script.
Please help me, any one.

Thanks in advance


texts: What is the best and most performant MySQL cloud solution?

texts:
I'm looking for a solid solution (~1GB of DB space). Speed is crucial and I'm happy to spend up to $100 a month on a decent solution. The database will be used by a series of desktop applications developed on .Net.

I've already tested both Xeround and Amazon EC2 solutions, but the results were similar and pretty disappointing.

I would highly appreciate any suggestion based on your experience.

Update:

I've recently tested an EC2 m1.xlarge and a RDS db.m1.large. Similar results as with micro instances. Tests were done from Europe and America. Results:

Europe to EC2/RDS - 22 min.
America to EC2/RDS - 8 min.
local to local - 10 sec.


texts:
I am trying to find the average value(val) for every month(1,2,3,4,...10) for a,b,c on the given cube below.

Here is my query:

select
  ([Measures].[VAL1],[DIMPROPERTY TIME].[TIMEDIM].[MONTH])  on 0,
{[DIMPROPERTYLENDER].[Hierarchy].[NAME]} on 1

from [PropertyData2016]

I am pretty new to this, so any help is appreciated.


texts: Select permission inside stored procedure?

texts:
I have granted a user execute permission for a stored procedure that uses dynamic SQL.  But when he tries to execute it, he gets the error:

  The SELECT permission was denied on the object '[table name]', database '[database name]', schema 'dbo'.

Does the user need to be granted permission for any tables that the stored procedure uses? That wouldn't really make any sense to me.


texts: MySQL queries are 10 to 100 times slower after OS reboot

texts:
I have a MySQL server running on Ubuntu. If I restart the MySQL, everything is fine. If I however restart the OS, my queries take anywhere from 10 times to 100 times as long. The only "solution" to the problem I have found is to run optimize on every table. Afterwards everything performs normal again. However, rebuilding the whole database after every OS reboot is obviously extremely painful and not a viable long term solution.

I reboot the OS by

stopping all programs querying the database
waiting for queries to finish executing
running stop mysql
making sure that the server stopped with ps -C mysql and ps -C mysqld
running reboot

If I leave out the OS reboot and just start up MySQL again, everything is fine.

Additional information:

The error log doesn't contain anything pointing to the issue, nor do I get any errors. Everything is just a magnitude or two slower.
All tables are affected. I am using InnoDB, but the very same problem existed when I was using MyISAM.
Usually MySQL uses 80% of the 8GB RAM, but after the OS reboot only about 1GB is used. CPU usage goes down from 30-80% of one core to about 1%.
The optimize query makes normal use of the full 80% RAM and CPU goes up to normal values, too.

Specs:

MySQL 5.5.35-0ubuntu0.12.04.2
Ubuntu 12.04.4 LTS
8GB RAM, 4 CPU cores

my.cnf file:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]

local-infile=0

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

innodb_file_per_table

innodb_autoinc_lock_mode = 0

innodb_thread_concurrency=8
innodb_buffer_pool_size=6G
innodb_log_file_size=1600M
innodb_additional_mem_pool_size=1M
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=0

innodb_read_io_threads=3000
innodb_write_io_threads=7000
innodb_io_capacity=10000

key_buffer_size     = 1024M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP

query_cache_limit   = 1M
query_cache_size        = 16M

log_error = /var/log/mysql/error.log

expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/

key_buffer_size is 1GB, that is about as much RAM being used after the reboot. Could that be connected?

Edit: When logging the query execution times per table, I have noticed that after repairing three of the four tables, sometimes the query times of the three repaired tables still go very high. So I am not completely sure that all tables break. Maybe it's just one table that is pulling down the performance of the whole server? However that doesn't really match the low % of used resources.


texts: SQL Server 2008 R2 Replication not working - my 1st try

texts:
I am having difficulties when I attempt my first shot at SQL Server 2008 R2 Replication.
Looks like I have login issues.
How do you modify settings without having to start a new plan again?
Please be kind - this is all new to me ....... lol

It looks like I have a security problem:

  Access to path is denied


texts:
From last few days, I am facing connectivity issues with MongoDB Replica Sets deployed in google cloud. Interesting fact is these issues are appeared at Development machines and not on the production. Most common issues are:

Cannot connect to replica set "xxx"[xxx:27017]. Set's primary is
unreachable.
A primary with different host name [:27017] found in
server side. Different members found under same replica set name
"XXX"

These error are appeared frequently. How to resolve this?

Here is the screenshot of the error appeared:


texts: Why do I have to select from the dual table?

texts:
This works in the major Relation Database Management Systems most likely to appear on StackOverflow/dba.stackexchange, being SQL Server, MySQL, PostgreSQL and SQLite (WebSQL).

select 'abc' abc, 1 def;

It does not work on Oracle.  Why do we need to select from DUAL in Oracle?  Does the ISO/ANSI standard for SQL require a FROM clause for SELECT statements?

Edit:

Per Bacon Bit's answer, it does seem required by the SQL standard.

So in reality, because the name DUAL is such a misnomer, if I were to create a table and name it ATOM or ONE, e.g. create table one (atom int); .. select 'abc' abc, 1 def FROM one; - Is there a performance penalty compared to SELECT .. FROM DUAL?


texts: Syntax to not write single statement to binlog?

texts:
I know you can SET SQL_LOG_BIN=0; to disable writing your session to the binlogs.

I was wondering if there was some single serving query syntax to prevent just that one query from being written w/o setting that session var to 0 and back to 1 afterwards?


texts: DB2 SQL: How to select all days in a date range even if no data exists for some days

texts:
I am really really new to the whole programming world.

I have found this previously. However I need rolling dates and into a SQL i am not aware what it is. Example of the SQL im using:

Select dd.date, dd.week, sum(p.policy_count) Policies,
from db.policies p
join global.dim_date dd on dd.date = p.trans_date
Where dd.date between (select week - 112 days from global.TODAY_DATE) and (select week - 1 day from global.TODAY_DATE)
Group by dd.date, dd.week

I tried plugging in the recursive CTE from the other thread but it errors out the whole time and I ma not sure of how to adapt it to DB2 language and make it rolling.

Any help much appreciated. Thanks

EDIT: I have been playing around and the temporary workaround is that we have a separate date table that in itself is counted as a value and can thus be joined as a union all to allow the 0 values to stand. However the closest I came without that is with:

select
dd.date, coalesce(sum(x.policy_count),0) policy_count, sum(x.POLICY_GROSS) TY_Gross, sum(x.POLICY_COMMISSION) TY_Comm

from global.dim_date as dd left join
( select p.trans_date, sum(p.policy_count) policy_count, sum(p.POLICY_GROSS) TY_Gross, sum(p.POLICY_COMMISSION) TY_Comm

from db.policies p join db.agents a on a.agent = p.agent

where p.trans_date between (select week - 112 days from global.TODAY_DATE) and (select week - 1 days from global.TODAY_DATE)
group by p.trans_date ) AS x on x.trans_date = dd.date

Where dd.date between (select week - 112 days from global.TODAY_DATE) and (select week - 1 day from global.TODAY_DATE)

group by dd.date

This does not work because it rejects p.policy_gross and p.policy_commission and returns the coalesce values as 1 instead of 0.

As per comment below I am not sure how to identify tables and I have asked around and no one knows what DB2 it is, I emailed the devs so will update that soon

EDIT 2: Tables

Dates = global.dim_date dd
Policies = db.policies p
Agents = db.agents a

When I add extra constraints after 'where dd.date..' e.g.

Where dd.date between (select date - 112 days from global.TODAY_DATE)
and (select date - 1 day from global.TODAY_DATE)
and a.agent in ('xxxxx','yyyyy')

When before all available dates, even with zeros, were returned are now omitted and i believe its because of the and clause.


texts:
I just realised you can't have a compound index with more than one array with mongodb.
I'm doing a tags,categories and actors search and I have simply stored them as arrays in the document example :

{
url = whatever.com
tags = [tag1,tag2]
actors = [actor1]
categories = [categorie1,categorie2]
}

How should I store them to make the search as fast as possible ? A search of this form:

tag1 AND tag 2 ... AND tagN AND categorie1 AND categorie2 ... AND categorieN AND actor1 AND actor2 ... AND actorn

Where the documents returned would be the videos that all have AT LEAST(so all) those criterias.


texts: DMZ server unable to connect through SQL Browser

texts:
I have a Web server in my DMZ, I have a SQL Server 2008 R2 server inside the LAN, this database server has named instance with a NO dynamic port. I have set as firewall rules the connection from my DMZ server and the database server in the SQL Server instance port and the UDP port 1434 for SQL Browser.

I thought this will be enough to allow the connection between the two servers, however I created a UDL file in my DMZ server and tried to connect to the SQL server instance and it failed, but when I included the Instance port in the string, it worked.

Please can you help me, I want to be able to connect to the named instance without including the Instance port, instead use the SQL Browser as it would be for other serves inside the LAN.

Thanks


texts: Why is our Non-Mapped Virtual memory usage high when running MongoDB (MMAP) on CENTOS

texts:
Our non-virtual virtual memory usage on our Linux MongoDb instances seems high, currently sitting just over 2GB. The instances are running a minimal installation of CENTOS (no GUI) and they acting as database servers, nothing else. We also have a Windows machine in the same replica set and that has considerably lower non-mapped virtual memory usage (210MB).

One suggestion from the MMS site is that lots of connections can cause this problem but both Linux machines only have about 16 connections. Can anyone explain what else might cause this high memory usage?


texts:
I've recently inherited a SQL Server 2017 instance with performance issues and I've been able to remediate the low hanging fruit. However, one substantial wait still remains.

The Query Store - qds_async_queue seems to be significantly high. But I'm unable to find any meaningful documentation or insight. Microsoft's online description has been at 'TDB' since SQL 2016.

Does anyone have any insight or helpful information about this wait?


texts: Can you force sets of rows to be unique?

texts:
Let's say we have a table like this with a primary key of (nr, key):

nr | key | val
---|-----|------
1  | 1   | a
2  | 1   | a
2  | 2   | b
3  | 1   | a
3  | 2   | c

Every set of rows (grouped by nr column) is supposed to be unique, as is the value of the key column within each of those groups. Additionally, no group may consist of rows with the exact same values for [key,val].

So inserting a row of VALUES (4,1,'a') or the group VALUES (4,1,'a'),(4,2,'b') would be invalid, because the former would duplicate the row with nr=1 and the latter the nr=2 grouping.

Is it possible to enforce that purely on the database? The only way I can think of is to use a procedure, but that still wouldn't stop anyone from inserting invalid rows manually, would it? Also, if you delete the row (2,2), then the nr=1 row would suddenly be duplicated by the group with nr=2.

Or am I going about this all wrong? I'm basically trying to group multiple sets of key=>value rows.

The unique constraint is basically GROUP_CONCAT(key,val) with a GROUP BY nr. But I don't see a way to express that in the schema. A change of DB is not out of question. I've looked into PostgreSQL partial indices, but they don't seem to help here, do they?


texts: Can not move MySQL data folderto external storage

texts:
OS Ubuntu 18.04, laptop, ssd partitioned for two OS (win8 and linux), second drive mounted with permissions. Line from fstab:

UUID={UUID} /media/iam/storage ntfs-3g permissions 0 1

I created dir db/ in root dir of second drive, than do following step by step exactly as desribed. (Try, I mean. All breaks on step 9):

Moving the MySQL data directory Ubuntu 16.04

Got this error

-- Unit mysql.service has begun starting up.
янв 04 14:39:10 thevoid audit[5063]: AVC apparmor="STATUS" operation="profile_replace" info="same as current profile, skipping" profile="unconfined" name="/usr/sbin/mysqld" pid=5063 comm="a
янв 04 14:39:10 thevoid kernel: audit: type=1400 audit(1546601950.205:134): apparmor="STATUS" operation="profile_replace" info="same as current profile, skipping" profile="unconfined" name=
янв 04 14:39:10 thevoid mysqld[5064]: mysqld: Can't change dir to '/media/iam/storage/db/mysql/' (OS errno 13 - Permission denied)
янв 04 14:39:10 thevoid mysqld[5064]: 2019-01-04T11:39:10.441016Z 0 [Warning] [MY-010091] [Server] Can't create test file /media/iam/storage/db/mysql/thevoid.lower-test
янв 04 14:39:10 thevoid mysqld[5064]: 2019-01-04T11:39:10.441064Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 5064
янв 04 14:39:10 thevoid mysqld[5064]: 2019-01-04T11:39:10.442897Z 0 [Warning] [MY-010091] [Server] Can't create test file /media/iam/storage/db/mysql/thevoid.lower-test
янв 04 14:39:10 thevoid mysqld[5064]: 2019-01-04T11:39:10.442905Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /media/iam/storage/db/mysql/ is c
янв 04 14:39:10 thevoid mysqld[5064]: 2019-01-04T11:39:10.442937Z 0 [ERROR] [MY-010172] [Server] failed to set datadir to /media/iam/storage/db/mysql/
янв 04 14:39:10 thevoid mysqld[5064]: 2019-01-04T11:39:10.442975Z 0 [ERROR] [MY-010119] [Server] Aborting
янв 04 14:39:10 thevoid mysqld[5064]: 2019-01-04T11:39:10.443075Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.13)  MySQL Community Server - GPL.
янв 04 14:39:10 thevoid systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
янв 04 14:39:10 thevoid systemd[1]: mysql.service: Failed with result 'exit-code'.
янв 04 14:39:10 thevoid systemd[1]: Failed to start MySQL Community Server.

I check permissions for folder mysql, they was 777 with root:root as owner. I change owner:

sudo chown -R mysql:mysql /media/iam/storage/db/mysql

iam@thevoid:/media/iam/storage/db$ ls -la
total 12
drwxrwxrwx 1 mysql mysql    0 янв  4 13:43 .
drwxrwxrwx 1 root  root  8192 янв  4 11:30 ..
drwxrwxrwx 1 mysql mysql 4096 янв  4 13:56 mysql

Then

sudo systemctl start mysql

and again got this error. What can I do with this? Why this can happen?


texts: Transactional Replication: Cannot continue the execution because the session is in the kill state

texts:
I have Transactional replication in one environment, I disable existing one and configuring it again, But While initialization in am getting following error

Distributor Subsystem: Cannot continue the execution because the session is in the kill state.

Same was working before with out any issues.

Can some onle help me on this?


texts: X5687 vs X5690 CPUs for a SQL 2008 R2 Server

texts:
I have the choice between:

2 x X5687 (Intel® Xeon® Processor X5687 - QuadCore
(12M Cache, 3.60 GHz, 6.40 GT/s Intel® QPI)

or
2 x X5690 (Intel® Xeon® Processor X5690 - SixCore
(12M Cache, 3.46 GHz, 6.40 GT/s Intel® QPI)

for a new DB Server (SQL 2008 R2 64bit), my personal preference is X5690, are there reasons that would speak against going for a SixCore CPU vs. a QuadCore CPU ?


texts: How do I determine if pgAgent is installed on a Postgres instance?

texts:
I have inherited some problems and I need to determine if pgAgent exists on some number of installations (CentOS) and is running background jobs. These jobs could be run from cron, pgAgent, or an external caller.

If I am looking at a postgres instance and not using pgAdmin, how can I determine if pgAgent is installed?

Can this be done from the command line or from the psql client? I'd prefer that to pgAdmin if possible.


texts:
I'm running postgresql 9.3 on a production server. An hour ago, out of the "blue", I ran into an issue I have never encountered before: my server started to use CPU as crazy. The server is a standard ubuntu 12.04 LTE installation running only Postgres and Redis.

The incident can be seen on the in numbers below:

I immediately took a look at pg_stat_activity but nothing in there seemed suspicious. I also had a look at the postgres log, but nothing was in there too.
I have pg_stat_statements running, so I reseted that one, and nothing really suspicious occurred in there, expect for the fact, that all queries were taking 100x times longer than usual.

I have tried the following with no luck:

Restart clients connecting to the db
Restart postgres
Restart the whole server

I have run memory tests on the server as well, and nothing seems to be wrong.

No changes in any software running on the servers has been made within the last 24 hours.

The question is:
I have a streaming replication server running, which I have now done a failover to, and it runs fine. However I still have no clue why my master suddenly has become so CPU consuming, and how I can debug / trace it further down?


texts: How to repair all indexes in a MySQL database?

texts:
My database got corrupted, and now the php app is throwing this error:

Invalid query: Can't find FULLTEXT index matching the column list

The error is not very useful, since it doesn't say what column/table is causing it.
Is there a way to regenerate FULLTEXT indexes on all columns in all tables within a specific database?

Thanks


texts: Only allow access to a table via a function, not directly via queries

texts:
I have a table t and some functions which access t. For example:

create function list_t() returns setof t as $$
    select * from t;
$$ language sql stable;

Is it possible to define role permissions to only allow the execution of list_t(), while not allowing plain SELECT, UPDATE, DELETE and INSERT queries on t?

I tried the following:

grant execute on all functions in schema public to my_user;
revoke all on t from my_user;

While this indeed disallows SELECT * FROM t, it also disallows SELECT list_t().


texts: Oracle: Keeping exact db clone synced up in real time

texts:
Is there a way to keep an exact clone of a database in real-time? (Or very close to real time)
The use case is for data reporting. I'm currently limited in it to make sure the application performance isn't impacted. If there was a real-time clone I could query then application performance becomes less of an issue.
I don't really know what to ask for in Oracle terminology when I approach our DBA folks about a solution, and things always go much more smoothly if I can speak their language from the outset without having to hash out precisely what I'm asking for.


texts:
I am attempting to run this SELECT statement, but I keep getting error -

  Msg 130, Level 15, State 1, Line 9
  Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

This is my syntax, how should this be re-written in order to succesfully execute?  I also add here make-up DDL to better illustrate question.

Declare @info TABLE (region varchar(50), p1 float, p2 float, p3 float, p4   float, p5 float, necombs float, d1 datetime)
INSERT INTO @info (region, p1, p2, p3, p4, p5, necombs, d1) VALUES
('west', 1.11, 2.22, 3.33, 4.44, 5.11, 22.00, '01/01/2015 08:00:00.000')
,('east', 2.11, 3.22, 3.33, 1.44, 2.11, 12.00, '01/03/2016 08:00:00.000')
,('north', 1.11, 2.22, 3.33, 4.44, 5.11, 22.00, '01/05/2015 08:00:00.000')

Select
region
,SUM(CASE WHEN YEAR(d1) = '2015' THEN ISNULL(p1, 0)+ISNULL(p2, 0)+ISNULL(p3, 0)+ISNULL(p4, 0)+ISNULL(p5, 0)/NULLIF(SUM(ISNULL(necombs, 0)), 0) ELSE 0 END) [Answer]
FROM @info
Group By Region


texts: alter primary key and all references in sql server

texts:
I'm trying to alter a primary key field but is being referenced by about 93 tables.

I used the following script to drop and create the FK constraints: https://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx

but I still need to alter all those referenced fields and they seem to reference others.

Anyone know an script that can handle all of that or knows a tool that can help me achieve this.

Thanks in advance


texts: How to track record on log_login when user can be deleted?

texts:
i really confuse to thinking my case. I design new db:

tbl_user(id, name, created)
tbl_log(id, user_id, action, created) //track user activity (delete, update, login, etc)

what i'm confusing is to set what rule in ON DELETE tbl_log.user_id.

what i want:

admin can delete user
tbl_log is readonly in apps even admin.
apps still can track/keep deleted user activity on db

.

if i set RESTRICT or NO ACTION

admin cannot deleted old user which no used anymore. This can cause garbage of user.

if i set NOT NULL or CASCADE

apps cant track old user activity in the database anymore.

  What i think that can solve my problem is

tbl_log(
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    user_id int NULL, //i change this to allow NULL
    user_name varchar(100) NOT NULL //add tbl_user.name to track record
    action varchar(255) NOT NULL,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

    INDEX `fk_tbl_user_tbl_log_idx` (`user_id` ASC) ,
    CONSTRAINT `fk_tbl_user_tbl_log`
    FOREIGN KEY (`user_id` )
    REFERENCES `mydb`.`tbl_user` (`id` )
    ON UPDATE NO ACTION
    ON DELETE SET NULL //set NULL if user deleted
) ENGINE = InnoDB;

before deleted
id  | user_id   | user_name     | action        | created
1   | 4         | john smith    | do something  | 2015-01-01 22:31:02

after deleted
id  | user_id   | user_name     | action        | created
1   | NULL      | john smith    | do something  | 2015-01-01 22:31:02

The problem is:

there is a chance if new user have same name with old user (but this not a big problem for my current apps, since i track record for user registered, so if old john smith did fraud action, new john smith can not be suspected, because tbl_log.created < tbl_user.created )
tbl_log.user_name will looks dont have any relationship with tbl_user.name

this is really easy for me using server side scripting (PHP). But, i'm literally blank how to do the right way in ERD. Any suggestion are welcome.


texts: How can I determine if a table exists in the current search_path with PLPGSQL?

texts:
I'm writing a setup script for an application that's an addon for another application, so I want to check if the tables for the other application exist.  If not, I want to give the user a useful error.  However, I don't know what schema will be holding the tables.

DO LANGUAGE plpgsql $$
BEGIN
    PERFORM 1
    FROM
        pg_catalog.pg_class c
        JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE
        n.nspname = current_setting('search_path')
        AND c.relname = 'foo'
        AND c.relkind = 'r'; -- not sure if I actually need this or not...

    IF NOT FOUND THEN
        RAISE 'This application depends on tables created by another application';
    END IF;
END;
$$;

However, current_setting('search_path') returns a TEXT containing "$user",public by default, which isn't terribly useful.

The only other thing I can think of is to try selecting from the table and catch the exception.  It would do the job, but I don't think it is very elegant and I've read that it is expensive to use (though maybe that would be ok in this scenario since I'm only running it once?).


texts: Capture row with highest value from each percentile

texts:
So far I've tried doing this:
SELECT MAX(buckets.position_id), NTILE
FROM  (
   SELECT position.position_id,NTILE(100) OVER (ORDER BY position.position_id)
   FROM position
   WHERE activity_id = 8
   ) AS buckets
GROUP BY  NTILE ORDER BY 2;

Which outputs something like this:
 max | ntile
-----+-------
   3 |     1
   6 |     2
   9 |     3
  12 |     4
  15 |     5
  18 |     6
  21 |     7
  24 |     8
  27 |     9
  30 |    10
  33 |    11
  36 |    12

But this just shows the id along with the percentile of that id in the row, I need to show the complete row every time, so I tried doing this:
SELECT MAX(buckets.position_id), NTILE
FROM  (
   SELECT position.position_id,NTILE(100) OVER (ORDER BY position.position_id)
   FROM position
   WHERE activity_id = 8
   ) AS buckets
FULL JOIN position ON position.position_id = buckets.position_id
GROUP BY  NTILE ORDER BY 2;

Which shows the same output.
SELECT MAX(buckets.position_id), buckets, buckets.ntile
FROM  (
   SELECT position.position_id, position.lat, position.long, position.activity_id, position.position_created_at , NTILE(100) OVER (ORDER BY position.position_id)
   FROM position
   WHERE activity_id = 8
   ) AS buckets
GROUP BY buckets, buckets.ntile
ORDER BY 3;

This outputs something like this, which is what I want, but it's repeating the NTILE instead of just getting the last one.
 max |                                       buckets                                       | ntile
-----+-------------------------------------------------------------------------------------+-------
   1 | (1,20,90,12,12,8,"2021-03-12 21:07:47.8646",1)                                      |     1
   2 | (2,20,90,2,12,8,"2021-03-12 21:08:57.59163",1)                                      |     1
   3 | (3,20,90,2,12,8,"2021-03-12 21:09:21.355993",1)                                     |     1
   4 | (4,20,90,2,12,8,"2021-03-12 21:09:36.752904",2)                                     |     2
   5 | (5,20,90,2,12,8,"2021-03-12 21:09:45.815941",2)                                     |     2
   6 | (6,20,90,2.001,12.001,8,"2021-03-15 12:11:36.320269",2)                             |     2
   7 | (7,20,90,2.001,12.001,8,"2021-03-15 12:11:38.876406",3)                             |     3
   8 | (8,20,90,2.002,12.001999999999999,8,"2021-03-15 12:11:41.911301",3)                 |     3
   9 | (9,20,90,2.002,12.001999999999999,8,"2021-03-15 12:11:44.938797",3)                 |     3
  10 | (10,20,90,2.0029999999999997,12.002999999999998,8,"2021-03-15 12:11:47.975407",4)   |     4
  11 | (11,20,90,2.0029999999999997,12.002999999999998,8,"2021-03-15 12:11:51.003433",4)   |     4
  12 | (12,20,90,2.0039999999999996,12.003999999999998,8,"2021-03-15 12:11:54.035314",4)   |     4
  13 | (13,20,90,2.0039999999999996,12.003999999999998,8,"2021-03-15 12:11:57.067232",5)   |     5
  14 | (14,20,90,2.0049999999999994,12.004999999999997,8,"2021-03-15 12:12:00.099153",5)   |     5
  15 | (15,20,90,2.0049999999999994,12.004999999999997,8,"2021-03-15 12:12:03.131785",5)   |     5
  16 | (16,20,90,2.0059999999999993,12.005999999999997,8,"2021-03-15 12:12:06.171215",6)   |     6
  17 | (17,20,90,2.0059999999999993,12.005999999999997,8,"2021-03-15 12:12:09.200098",6)   |     6

But since those values change every row, the Max function messes up and it just shows every row instead of the last percentile...
I also tried wrapping everything inside MAX() functions, but I'm afraid that when I get a lower value from either lat or long it's going to mess up. (Also I don't think that's very efficient, correct me if I'm wrong.)
DB-fiddle for testing
https://www.db-fiddle.com/f/gKoqqmRa47g5546EMz5Hs/0


texts:
We are trying to migrate a database from Sybase ASE Server 15.0.3 to SQL Server 2016 with the help of a migration assistant application. We tried doing this by creating sample databases in the Sybase Server and then migrating it to the SQL Server 2016 but we were unable to test for all the components due to lack of a proper database.

Let us know if there is anything that can be done for the following:

Where can we find real time databases or sample databases to work on?
What is the migration strategy to follow in this scenario?
Is there any reference for the steps involved in the migration process?


texts: Want to match two date columns of two tables both have diff formats in mysql

texts: One column format is like '2005-03-22' and other is like '2005-03-22  22:09:01'.. i tried to_date function but its not working in mysql..which function should i use?


texts:
I've followed this instructions, but after mysqld --skip-grant-tables --user=mysql, I'm getting this:

110913 20:29:18 [Warning] Can't create test file /var/lib/mysql/my-laptop.lower-test
110913 20:29:18 [Warning] Can't create test file /var/lib/mysql/my-laptop.lower-test
mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 2)
110913 20:29:18 [ERROR] Aborting
110913 20:29:18 [Note] mysqld: Shutdown complete

what I think it could be normal because the tutorial ask to do "rm -R /var/lib/mysql" on the step before. So I then create /var/lib/mysql, execute again "mysqld --skip......" and I get this:

110913 20:32:07 [Note] Plugin 'FEDERATED' is disabled.
110913 20:32:07  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

This is my /etc/my.cnf:

 # The following options will be passed to all MySQL clients
[client]
user            = mysql
#password    = your_password
port        = 3306
socket        = /var/run/mysqld/mysqld.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir         = /usr/local/mysql
datadir         = /usr/local/mysql/data
tmpdir          = /tmp
log_error       = /var/log/mysql/error.log
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

I have ubuntu 10.10.

Any idea?

Javi


texts:
Hi I'm creating a very simple application that logs activities.

Right now I'm thinking I would just do

{"Activity":[
  {"ActivityType":"Sleep"},
  {"time_begin": someunixtime, "time_end":someotherunixtime},]}

and from there I could retrieve and calculate the date and the duration.

is that a sound decision?


texts: MySQL: index statistics dropped when table dropped?

texts:
When a table is dropped, are its index statistics also removed?

The reason I ask is that I was hoping to 'prep' the stats by running log playback via the percona-playback tool prior to cutting over from our current production environment.

Thanks.


texts: Usage of json to store multiple attributes

texts:
Typically we have the table designed like this

UserId  FirstName LastName EmailId  MobileNumber Age Sex City

Where UserId is going to be the primary key

Is it good to have multiple columns combined into one single column?
i.e what if i have the above table structure changed to one below

UserId  EmailId UserDetails

Where the UserDetails will contain all the user details in a json format.

When we are querying, we can bring the data and build the user object from json string and get the desired results using Linq to Sql queries

What are the problems we might face if we follow this approach?


texts: SQL Server AlwaysOn Availability Group - Creating an Index Online in SSMS

texts:
I am running a 2-node SQL Server 2016 AlwaysOn Availability Group.  I would like to create a non-clustered index online in SSMS for one of the availability databases in the AlwaysOn Availability Group.  For this operation, it is very important that the index create be Online; in other words, the table should not become locked and both reads and writes should continue without interruption or error during this time.  Also, the cluster should not go into an unhealthy state where replication stops.

Here was my plan for doing this:

Go into SQL Server Management Studio (SSMS) on the master node, go to the database, table and then Indexes section in Object Explorer and right-click on Indexes and choose New Index - Non-Clustered
Add the appropriate field to the Index Key columns screen
Go to Options - and set "Allow online DML processing" to "True"
Click OK

Is the above approach the best way to do this?  Will this actually create the index online?  While the index is being created, will the cluster remain in a healthy and replicated state, will the index get created on the secondary automatically, and will the incoming reads and writes continue to happen without errors?

Any help with this is greatly appreciated!


texts:
How can I log from within an SSIS Script Component?

I am using the below code, which I believe should work.

public override void PreExecute()
{
    this.Log("Entering PreExecute()", 0, new byte[0]);
    base.PreExecute();
}

public override void PostExecute()
{
    this.Log("Entering PostExecute()", 0, new byte[0]);
    base.PostExecute();
}

I followed the Ninja clicking method in SSIS Logging to get the below logging working for a Script Task in the same SSIS Package.

public void Main()
{
    Dts.Log("Entering Main", 0, new byte[0]);
    Dts.TaskResult = (int)ScriptResults.Success;
}

I believe that I have SSIS Logging configured correctly since I see the "Entering Main" log entry.


texts: How to simplify repeating variables in where clause for multiple columns SQL Server

texts:
I know there has to be a better way to evaluate a set of repeating variables against multiple columns. I've been searching for a while and still don't have a great solution. Is there a better way to do this?

SELECT *
FROM TABLE
WHERE
  (SUBSTRING(COLUMN1,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30)
OR SUBSTRING(COLUMN2,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30)
OR SUBSTRING(COLUMN3,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30)
OR SUBSTRING(COLUMN4,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30)
OR SUBSTRING(COLUMN5,1,3) IN ('val1','val2','val3','val4'...REPEATS to 30))
...REPEATS 5 MORE TIMES


texts:
I have multivalue attribute which I need to store in 2 different tables. It represents qualificiations for certain jobs. One table is for workers profiles and another for company. Company table states which qualification it needs. Profile of worker table stores data about his qualifications.

My problem is how to store qualification attribute so I dont have two tables which both have full list of all qualifications.


texts: Query sometimes hangs (but can be executed in less than 4s when run in parallel)

texts:
I have a Flask API that has 2 endpoints.
When the user clicks a button, the front-end first calls a /calculate route, which computes an expensive query and appends the results to a table called results.
This table is indexed as follows:
 create index if not exists results_tbl_idx on results
              (customer_id, result_id, team_id, team_name, territory_name, specialty, id);

When /calculate returns, it then gets a handle to the result_id, and then calls a /get_results endpoint, which runs the following query on the results table:
with distinct_users_team_tbl as (
    select team_id,
        count(distinct id) as distinct_users_team,
        count(distinct id) filter ( where targeted_users > 0 ) as targeted_distinct_users_team
    from tam_results_14
    where result_id = 201 and customer_id = '1'
    group by team_id
),
distinct_users_territory_tbl as (
    select team_id, territory_name,
        count(distinct id) as distinct_users_territory,
        count(distinct id) filter ( where targeted_users > 0 ) as targeted_distinct_users_territory
    from tam_results_14
    where result_id = 201 and customer_id = '1'
    group by team_id, territory_name
),
distinct_users_specialty_tbl as (
    select team_id, team_name, territory_name, specialty,
        sum(tam) as tam,
        sum(sam) as sam,
        count(distinct id) as  distinct_users_specialty,
        count(distinct id) filter ( where targeted_users > 0 ) as  targeted_distinct_users_specialty
    from tam_results_14
    where result_id = 201 and customer_id = '1'
    group by team_id, team_name, territory_name, specialty
)

select *
from distinct_users_specialty_tbl
join distinct_users_team_tbl
    on distinct_users_specialty_tbl.team_id = distinct_users_team_tbl.team_id
join distinct_users_territory_tbl
    on distinct_users_specialty_tbl.team_id = distinct_users_territory_tbl.team_id
    and distinct_users_specialty_tbl.territory_name = distinct_users_territory_tbl.territory_name

Sometimes, just after running /calculate, when fetching /get_results, the query hangs forever.
Typically, the /get_results query executes in < 4s. And even while it's hanging, I can paste the exact same query that is hanging into a console and run it in < 4s.
I have checked pg_stat_activity and can see the query running with no wait_event and state = active.
I then checked pg_locks and for that pid, I can see:

My current hunch
Is it that database hasn't finished indexing the new entries to the results table before I query it? But that it has finished adding items to the index by the time I paste the hung query into the terminal and run which can use the index?
Is there some way I can force the table to fully index before running the /get_results query? Is that even possible?
Thanks for any help!!!


texts: What is the history of the SQL Server ANSI (ISO) compatibility settings?

texts:
I know what these settings do and when to use them, what I'd really like to know is why this problem even exists.
Recently, I came across an ugly situation--make that, yet another ugly situation--involving the settings of what I'm goint to call the ANSI Seven:
ansi_nulls                ON
ansi_padding              ON
ansi_warnings             ON
arithabort                ON
concat_null_yields_null   ON
numeric_roundabort       OFF
quoted_identifier         ON

I've listed them above with what I believe to be the most commonly recommend default settings. (Of interest, SET ANSI_DEFAULTS will set all of them ON or OFF, without special treatment for numeric_roundabort, but I digress.)
These impact code, queries, query plans, and a whole host of stuff as can be found by searching for any of them throughout SO. There are so many hits, they obviously have caused pain and anguish to hundreds, if not thousands, of developers for decades. (I hit issues with them long before SO was there to help.)
What I would like to know is: why?
Why is this an issue? Where did these settings come from? Who thought it was a good idea? Most importantly, why are these seven pretty much always lumped together?
Does anyone have any insights on this? Links to articles, blogs, even Learning Channel Documentaries would be great.


texts: Create result set with all records from LEFT table, and from the RIGHT table only bring records that do no exist in left already

texts:
I'm having a bit of trouble wrapping my head around a way to make this JOIN work.

Postgres 11.5

I have two tables:

Table1:
customer_id
date
(various other fields)

Table2:
customer_id
date
(various other fields)

The tables are not the same, but the customer_id's and dates are in the same format so will be able to be joined.

I am trying to create a result set that contains both of the tables combined together (all columns) but with the following logic applied:

All data from Table1
Only records from Table 2 that DO NOT exist in Table 1
Joined on customer_id and date

My end goal is that I am trying to create a stacked area chart with this result set, but there are situations where a customer could be both in Table 1 and Table 2, so for a single given day, if someone exists who is like that, I don't want to double count that individual.

Appreciate the help!


texts: taking a gcloud snapshot of a postgres database

texts:
Given a postgres database on a Google cloud virtual machine instance, are we guaranteed snapshot consistency if we call pg_start_backup(), sync(), then take a snapshot of the instance?

https://cloud.google.com/compute/docs/disks/create-snapshots

The documentation says this is true in "most situations", but I'm not sure how to determine what conditions would cause inconsistencies.


texts: MySQL MSI appears to be installing wrong (latest) MySQL version

texts:
For compatibility reasons with other software, I'm trying to install MySQL 5.6.40 on Windows 10. Any version from 5.7 onward is incompatible with my other programs.

I've been trying to use the MSI installer but - no matter what version of 5.6 I try to install - the only server option I get is 8.0.

The installer has the correction version in its name (e.g. (mysql-installer-web-community-5.6.41.0.msi) but when I run it, I only get version 8.0.11 appearing for installation. I've gone into the Add screen, and there's no other versions showing under server. There doesn't seem to be the ability to point to a different version during installation.

I get the same problem if I try the normal download site or the archive site.

I would prefer to use the MSI installer rather than downloading the zip files, as I am not confident with installing a database from a zip.

I've been searching to see if this has been reported by others, and I haven't found any comments on this.

I would appreciate any help.


texts: Why is my database still fragmented after I reorganized and rebuilt it, and updated the statistics in SQL Server?

texts:
I created a Maintenace Plan, first i reorganize the indexes, then rebuild, and final update the statistics every Saturday.
I ran this script for verification:
SELECT
    S.name as 'Schema',
    T.name as 'Table',
    I.name as 'Index',
    DDIPS.avg_fragmentation_in_percent,
    DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
    INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
    INNER JOIN sys.schemas S on T.schema_id = S.schema_id
    INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
        AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
    AND I.name is not null
    AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent DESC

But after the run, the fragmentation is still very high. Is that okay? What could be done?
Thank you for your help! :)


texts: I need to Truncate a table in Sql server which containing around 250GB data but before truncating i need to take backup of this,it may to csv file or other mode. What is best way to do it.


texts: SQL Backups - Full vs Differential vs Log Shipping

texts:
I'm looking to refine our SQL backups utilizing Full, Differential, and Log Shipping.  Currently, I am only performing full backups once at night.  This process is taking so long that it is running throughout most of the work day.  As we would like to use our standby server for reporting, this is not ideal.

I would like to increase the speed of our backups, but at the same time make things a bit more reliable.  What would be the best way to accomplish this with the above 3 methods?  Full backups once weekly, differential backups once daily, and log shipping every 15-30 min?

What considerations should I be making here? (Sorry this is a bit vague, just trying to get some input)

I am using SQL Server 2005


texts: Are there any objective reasons to prefer a prefix over a suffix for an environment-specific login?

texts:
When creating environment specific logins (development, qa, staging, production, etc.) I'm faced with the choice of making the denotation a prefix or a suffix to the login name.
Examples
prod_MyAppLogin
MyAppLogin_prod

Is there any reason why I should prefer one strategy over the other?
(Obviously, I should be consistent with whichever choice I make)


texts:
With SQL traces I was able to analyze them with Database Engine Tuning Advisor to obtain basic recommendations for perf. tuning(missing indexes, statistics,...).

Now, with SQL 2012 and Extended Events, how can I do something similar?

Thanks


texts: SLAVE off when loading data into the MASTER from another database

texts:
I have an active MASTER/SLAVE replication (Mariadb 10.2) with a single database (see scenario 1)

I decided to add in IMPORT database to populate the MASTER database as described in scenario 2. However, the SLAVE instance go OFF and “Unknown database import” error is traced back.

To overcome this, I created then a database with same name in SLAVE server (import) and the error is gone (see scenario 3).

However, the created database (slave import) was without tables (in purpose) so to be sure that data is inserted in the slave I tested by creating a test table in the MASTER database and I did insert data from the Master's import database.

As a result, data was obviously available in the slave database even if the slave's import database has no tables.

So basically, we need just the same db name in slave section to get worked without the need of tables.

Could you please explain to me how it works ?and why we need the same db in slave if its a row-based format ?


texts:
I have this simple MySQL query:

SELECT * FROM table1
INNER JOIN table2 ON table2.col = table1.id
WHERE table1.id = '123'

id is the primary key in table1.

table2 has composite primary key of two columns: col and col_two.

Now I ran EXPLAIN on the above query to see how it performs and got the following rows:

id select_type table   type  possible_keys  key     key_len  ref    rows Extra
1  SIMPLE      table1  const PRIMARY        PRIMARY 8        const  1
1  SIMPLE      table2  ref   PRIMARY        PRIMARY 8        const  1    Using index

From my understanding, ref would be used if the join matches multiple rows but I'm using WHERE with a constant value. So, shouldn't the join have a const type like the table1 query?

I'm worried that this would cause performance issues on a large scale. Is there a way to improve the above query or it's already good enough?


texts:
I'm trying to create a query that shows if students are mature or not, based on their date of birth. However when the query is executed it returns all students as not mature. Any students that are born before 1/10/1971 should be classed as mature.

Shown below is the IIf() statement I have used to show if students are mature.

When executed the table returns the following results

Can anyone give any idea as to why this is happening?

Shown below is the full page of the Query


texts: After uninstall oracle, there is remaining directory. 'tfa'

texts:
After uninstall ORACLE is complete, I can see 'product' directory is removed and I checked sqlplus is not available anymore. The final message looked fine.

However, there is a remaining or replaced directory, which is named tfa. I've searched it and it is a kind of tool to help us find out what's going on.

My questions are:

Why isn't it uninstalled?
Is it installed automatically at the same time of ORACLE install?
Can I remove it with rm command?
After uninstall, what kind of things do I have to check?

My ORACLE version is 12c.


texts: Restore Database wizard won't open in SSMS 2014

texts:
On a dev machine running Windows Server 2012 R2, I just upgraded SQL Server from 2012 to 2014.

Using SQL Server Management Studio 2014, when I right-click a database and go to Tasks -> Restore -> Database... nothing happens. The Restore Database wizard does not appear as expected.

No errors or crashes are in Event Viewer. The cursor doesn't even turn into an hourglass.

Using T-SQL to restore works fine, and if I go back and use SSMS 2012 it works fine. Any ideas on how to get the wizard to work in SSMS 2014?

I am connecting to the same instance of SQL Server in both versions of SSMS, and I have tried running SSMS as an administrator.

It is Service Pack 1:

Microsoft SQL Server Management Studio 12.0.4100.1
Microsoft Analysis Services Client Tools 12.0.4100.1
Microsoft Data Access Components (MDAC) 6.3.9600.17415
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 9.11.9600.18125
Microsoft .NET Framework 4.0.30319.34209
Operating System 6.3.9600