texts: Merge Only Events that Start immediately after the Previous Event without any Gap

texts:
How can i merge only the dates occur subsequently.?

(i.e, The Next Event begins immediately after the previous event completed).

Here, we need to consider some other Columns also for getting the Output.

Here is the Attached Screenshot of Sample Events Gantt Chart.

The following table contains events (M_ID, Y_ID, T_ID, E_ID, BeginDate, EndDate respectively)

CREATE TABLE #EventTable
(
    M_ID INT,
    Y_ID INT,
    T_ID INT,
    E_ID INT,
    BeginDate DATE,
    EndDate DATE
);

The Below Insert Statements for Sample Data Setup.

INSERT INTO #EventTable
VALUES
(135709, 2, 7, 1, '01 Jan 2017', '31 Mar 2017'),
(135709, 2, 7, 1, '01 Apr 2017', '30 Jun 2017'),
(135709, 2, 7, 3, '01 Jan 2017', '31 Jan 2017'),
(135709, 2, 7, 3, '01 Feb 2017', '30 Apr 2017'),
(135709, 2, 7, 3, '01 May 2017', '31 May 2017'),
(135709, 2, 7, 3, '01 Jul 2017', '31 Aug 2017'),
(135709, 2, 7, 3, '01 Oct 2017', '31 Oct 2017'),
(135709, 2, 7, 3, '01 Dec 2017', '31 Dec 2017'),
(135709, 5, 8, 1, '01 Feb 2017', '30 Apr 2017'),
(135709, 5, 8, 1, '01 Apr 2017', '31 Jul 2017'),
(134560, 5, 8, 3, '01 Apr 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 May 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 Oct 2017', '31 Oct 2017'),
(134560, 5, 8, 3, '01 Nov 2017', '30 Nov 2017'),
(135678, 3, 6, 2, '01 Jan 2017', '31 Mar 2017'),
(135678, 3, 6, 2, '01 Apr 2017', '30 Jun 2017'),
(135678, 3, 6, 2, '01 Oct 2017', '31 Oct 2017'),
(135678, 3, 6, 2, '01 Nov 2017', '31 Dec 2017'),
(123457, 4, 2, 3, '01 May 2017', '31 Oct 2017');

The Required Output Dates (Marked as RED) is mentioned in the above Screenshot.

Example:

For M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 1;

The 2nd Event starts on (01 Apr 2017) immediately after the 1st Event completed on (31 Mar 2017).

So, In this case, The Output should be BeginDate of 1st Event(01 Jan 2017) and EndDate of 2nd event(30 Jun 2017).

Similarly,
For M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 3;

The 2nd Event starts on (01 Feb 17) immediately after the 1st Event completed on (31 Jan 17).

The 3rd Event starts on (01 May 17) immediately after the 2nd Event completed on (30 Apr 17).

So, In this case, The Output should be BeginDate of 1st Event (01 Jan 17) and EndDate of 3rd Event (30 Jun 17).

And As there is GAP (i.e, 4th event doesn't start next to 3rd event) between the 4th event StartDate and 3rd Event EndDate, The Dates should remain same.

Can Someone help me out.?


texts: Web server accessing the database server in Always on availability group

texts:
We have a web server that is on the DMZ but that will need to query a database that resides inside the network. Now the network team wont allow port 1433 from the web server to the Database server.
The database server is on the Always on availability group and uses a specific listener port. Now my question is if my network team only allows only the Always on availability group listener port will that be a problem and what if the network team dont allow access to that port what are my options.


texts: How to analyze postgres client process/session memory

texts:
Is there a way to check how much of process memory is allocated for the specific buffer for a running postgres session (work memory, temp buffers, plan cache etc ... )?

I'm asking because i want to check what's eating memory of one running query (it raises to couple of GB). One time it happened to be just an SQL that had more than 4GB of bind variables and just loading that sql caused OOM of the process.
Other times it could be workmem going crazy due to huge number of sorts.


texts: drastically different runtimes in BIDS and the SSRS Web Portal

texts:
I have a report in a BIDS project that has been deployed to our SSRS Web Portal.  On the web, it renders within seconds.  In BIDS, it takes upwards of seven minutes.  I can see that the query runs against the database quickly - what in the world is BIDS doing that is taking so long?

I have seen this happen in a number of different cases...


texts:
Drop procedure if EXISTS rotatepjp;
DELIMITER //
CREATE PROCEDURE rotatepjp()
 BEGIN
  DECLARE u_id, settingval, pjp_userid, pjp_beatid, u_frequency INT;
  DECLARE  u_pjpstartdate, pjp_fordate, tilldate, newdate DATE;
     SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
     SELECT value INTO settingval FROM settings where name = 'autorotatepjp';
     IF settingval > 0 THEN
       BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE cur1 CURSOR FOR SELECT id, pjpstartdate, frequency FROM users;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        #open cursor
        OPEN cur1;

        #starts the loop
        the_loop: LOOP

            #get the values of each column into our variables
            FETCH cur1 INTO u_id, u_pjpstartdate, u_frequency;
            IF done THEN
                LEAVE the_loop;
            END IF;
            #Do some post processing
            IF u_frequency > 0 THEN
                SET tilldate = DATE_ADD( u_pjpstartdate, INTERVAL u_frequency DAY );
                IF tilldate = CURDATE() THEN
                    BEGIN
                        DECLARE done1 INT DEFAULT FALSE;
                        DECLARE cur2 CURSOR FOR SELECT user_id, beat_id, fordate FROM pjps where user_id = u_id and fordate >= u_pjpstartdate and fordate < tilldate;
                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
                        OPEN cur2;
                        the_loop1: LOOP
                            #get the values of each column into our variables
                            FETCH cur2 INTO pjp_userid, pjp_beatid, pjp_fordate;
                            IF done1 THEN
                                LEAVE the_loop1;
                            END IF;
                            SET newdate = DATE_ADD( pjp_fordate, INTERVAL u_frequency DAY );
                            INSERT INTO pjps (user_id, beat_id, fordate, created, modified )
                            VALUES ( pjp_userid, pjp_beatid, newdate, NOW(), NOW() );
                        END LOOP the_loop1;
                        UPDATE users set pjpstartdate = CURDATE(), modified = NOW() where id = u_id;
                    END;
                END IF;
            END IF;
        END LOOP the_loop;
     END;
END IF;
COMMIT ;
END //
DELIMITER ;

this code is working in innodb_version 5.5.34 but not working innodb_version 5.6.21.......any clue, where I am doing mistake???

error - #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1


texts:
I'm slowly collecting and trying to analyze lot of information on all of our instances (2008R2 SP1). I'm now going through the Error Logs and I've couple of question. There is a lot about error log size and found different recommendations on using sp_cycle_errorlog to recycle the logs on say, weekly basis, to avoid having very big error logs and be able to handle them adequately. So...

When an error log is considered to be "big"? Some of our logs take long to load, close to a million of rows. edit: one has 2 034 546 rows
I'm trying to find some tsql to get error log size, but can't find it, it is possible to do it?
I see the recommendation to recycle the logs and to configure more than just the default number of 6. Some authors recommend 50, others 10. I guess it depends on the environment, but is there a recommended number?


texts: Does a zero value in innodb_index_stats.rows_per_key mean the index is over-specified?

texts:
I'm taking care of an elderly 5.5.41 Mysql database and I was taking a look at the indexes with the following query:

select i.table_name, i.index_name, i.rows_per_key,
    coalesce(t.rows_read, 0) as tbl_rows_read,
    coalesce(s.rows_read, 0) as idx_rows_read
from information_schema.innodb_index_stats i
left join information_schema.table_statistics t using (table_schema, table_name)
left join information_schema.index_statistics s using (table_schema, table_name, index_name)
where i.table_schema = 'my_schema'
order by i.table_name, coalesce(s.rows_read, 0) desc, i.index_name;

For the rows_per_key column, some of the tables return values like 9234487, 9234487, 4273, 2, 0, 0. I take this to mean that once a query  specifies the first four columns in a SELECT, whether or not the fifth and sixth columns of the index are specified, the index selectivity does not improve.

The only time it could be useful is if only the fields in the index are sufficient to answer the query, then the planner does not need to consider the table at all.

But apart from that, if I redefine the index to contain only first for fields, I won't lose in performance?

By extension, since the index is then held in fewer pages, it is even more likely to be buffered in its entirety.


texts:
One of my jobs is failing and it shows below message.

Error:

Could not allocate a new page for database 'Reporting' because
of insufficient disk space in Filegroup ‘PRIMARY’.
Create the necessary space by dropping objects in the filegroup,
or setting autogrowth on for existing files in the filegroup.
[SQLSTATE 42000] (Error 1101) The step failed.

Any idea how can this be resolved.

Thanks,
Ar


texts: Query to find the 'Branch_Code' of the 'Channel_Num' with the lowest 'Issue_Date' using T-SQL

texts:
Hope you're doing well

I have a table with the structure below :

Customer_Channel_Branch:(Customer_Num int ,Channel_Num int ,Branch_Code int ,Issue_date  datetime)

Some Example Data :

                  "Customer_Channel_Branch"
---------------------------------------------------------
Customer_Num     Channel_Num    Branch_Code    Issue_date
    x                 1             1000         1990
    x                 2             2000         1991
    x                 3             3000         2000

There is another table with this structure below:

 Channel_general_Code:(Channel_Num  int , Channel_gnrl_Code  int)

some example data for this table:

            "Channel_general_Code"
-------------------------------------------
     Channel_Num     Channel_gnrl_Code
        1                 1
        2                 1
        3                 1

As you can see in the second table , all three Channel_Num --> (1,2,3) are grouped as one single channel . So what I want in the result set is this :

                    "result"
----------------------------------------------
Customer_Num     Channel_Num     Branch_Code
    x               1               1000
    x               2               1000
    x               3               1000

for Channel_Nums that are considered as one , (have the same Channel_gnrl_Code in the second table), I need the Branch_Code of the Channel_Num with lowest Issue_date.
I thought that I could use window functions

(`row_number() over(partition by Channel_gnrl_Code order  by Issue_date )`)

to write this query but it gave me a false result.
I was wondering if you could help me with this.

Thanks in advance


texts: How to prevent a PostgreSQL trigger from being fired by another trigger?

texts:
I have 2 triggers on one table; one works for INSERTs :

CREATE TRIGGER "get_user_name"
AFTER INSERT ON "field_data"
FOR EACH ROW EXECUTE PROCEDURE "add_info"();

This updates some values in the table.
And one for UPDATEs (to fill a history table):

CREATE TRIGGER "set_history"
BEFORE UPDATE ON "field_data"
FOR EACH ROW EXECUTE PROCEDURE "gener_history"();

The problem is that when I insert a new row in the table the procedure "add_info"() makes an update and therefore fires the second trigger, which ends with an error:

ERROR:  record "new" has no field "field1"

How can I avoid this?


texts: How to delete orphan temp tables in Postgresql 9.6

texts:
I noticed in my log a few entries about

autovacuum: found orphan temp table "pg_temp_35"."sometable" in database "mydb"

I tried to drop them but got:

(could not Retrieve the result : ERROR:  could not open file "base/19269/t24_752462": No such file or directory)

I've read on the mailing lists that this is a known possibility and that the developers don't want the server to just delete the orphaned tables automatically but I can't find info on how to fix the error that I'm actually getting.


texts: Max number of tables (objects) in SQL Server editions (compact, express)

texts:
What are the maximum number of tables that you can have in:

SQL CE4
SQL 2008 R2 Express

Numbers here http://msdn.microsoft.com/en-us/library/ms143432.aspx I guess is for SQL Server 2008, but are they valid for Express and what about SQL CE4?

  The sum of the number of all objects in a database cannot exceed 2,147,483,647


texts: COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

texts:
I'd like to do the following in one query using MySQL:

grab a row that has a parent_id of 0
grab a count of all the rows that have a parent_id of the row that we grabbed which has a parent_id of 0

How can I accomplish this in one query? Please let me know if you need more information, I will gladly be as assistive as I can. I'm not an expert on creating questions so please tell me what more information you need.

Here's an example of what I'm doing now:

select id from messages where parent_id=0

and then

select count(id) from messages where parent_id={{previously_chosen_id}}

How do I get a one shot query? Something like...

select id, count(records where parent_id=the id we just asked for)

Or, is there a better way to handle this? You see, currently I have to run a ton of queries to find the counts, when I'd rather do it in one shot.


texts:
Following on from a recent question that I asked on Tempdb. I was wondering how to correctly set the autogrowth property when adding and moving the tempdb files?

I ask because I would like to let SQL Server use the round-robin algorithm to spread the workload out across each of the files. I understand that if the files are not of equal size, then SQL Server will use the file with the most space, thus increasing the chances on causing latch contention. Is my understanding here correct?

Cheers,


texts: I moved a database from SQL Server 2005 to SQL Server 2012. Is there any way of restoring all the logins on the new server?


texts: Changes to estimates on predicates that contain SUBSTRING() in SQL Server 2016?

texts:
Is there any documentation or research about changes in SQL Server 2016 to how cardinality is estimated for predicates containing SUBSTRING() or other string functions?

The reason I'm asking is that I was looking at a query whose performance degraded in compatibility mode 130 and the reason was related a change in the estimate of the number of rows that match a WHERE clause that contained a call to SUBSTRING(). I corrected the problem with a query rewrite, but am wondering if anyone is aware of any documentation about changes in this area in SQL Server 2016.

Demo code is below. The estimates are very close in this test case, but the accuracy varies depending on the data.

In the test case, in compat level 120, SQL Server appears to be using the histogram for the estimate, whereas in compat level 130 SQL Server appears to be assuming a fixed 10% of the table matches.

CREATE DATABASE MyStringTestDB;
GO
USE MyStringTestDB;
GO
DROP TABLE IF EXISTS dbo.StringTest;
CREATE TABLE dbo.StringTest ( [TheString] varchar(15) );
GO
INSERT INTO dbo.StringTest
VALUES
( 'Y5_CLV' );
INSERT INTO dbo.StringTest
VALUES
( 'Y5_EG3' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_NE' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_PQT' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_T2V' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_TT4' );
INSERT INTO dbo.StringTest
VALUES
( 'ZY_ZKK' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_LW6' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_QO3' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_TZ7' );
INSERT INTO dbo.StringTest
VALUES
( 'ZZ_UZZ' );

CREATE CLUSTERED INDEX IX_Clustered ON dbo.StringTest (TheString);

/*
Uses fixed % for estimate; 1.1 rows estimated in this case.
    Plan for computation:
        CSelCalcFixedFilter (0.1) <----
            Selectivity: 0.1
*/
ALTER DATABASE MyStringTestDB SET compatibility_level = 130;
GO
SELECT *
FROM dbo.StringTest
WHERE SUBSTRING(TheString, 1, CHARINDEX('_',TheString) - 1) = 'ZZ'
OPTION (QUERYTRACEON 2363, QUERYTRACEON 3604);

/*
Uses histogram to get estimate of 1
 CSelCalcPointPredsFreqBased <----
      Distinct value calculation:
          CDVCPlanLeaf
              0 Multi-Column Stats, 1 Single-Column Stats, 0 Guesses
      Individual selectivity calculations:
          (none)
    Loaded histogram for column QCOL: [DBA].[dbo].[StringTest].TheString from stats with id 1
*/
ALTER DATABASE MyStringTestDB SET compatibility_level = 120;
GO
SELECT *
FROM dbo.StringTest
WHERE SUBSTRING(TheString, 1, CHARINDEX('_',TheString) - 1) = 'ZZ'
OPTION (QUERYTRACEON 2363, QUERYTRACEON 3604);

/*
-- Simpler rewrite; works fine in both compat levels and gets better estimate.
SELECT *
FROM dbo.StringTest
WHERE TheString LIKE 'ZZ[_]%'
OPTION (QUERYTRACEON 2363, QUERYTRACEON 3604);
*/


texts: SQL Server 2008 R2 high Buffer I/O causes

texts:
Am trying to troubleshoot performance on a SQL Server that we have for our document management server and today performance has hit an all time low.

Looking at the Management Data Warehouse (MDW) I setup recently, I can see a huge spike in the Buffer I/O.  The combined SQL wait time is in the region of 1500-2000 ms which is considerably higher than normal (around 500ms).  The large proportion of this increase is Buffer I/O.

Not being a DBA and doing this out of necessity, I am massively out of my depth.

Is there any way to tell why there has been a large increase in Buffer I/O?  Is this likely due to a query pulling large amounts of data or incorrect config of SQL Server memory limits?  Is there anything specific (or general for that matter) I should be looking at or DMVs I can query to help troubleshoot?

The server is SQL Server 2008 R2. It is a VM running two vCPUs and 8GB RAM with the disks hosted on a separate array on a SAN.

The graphs above were all generated using SQL Server Management Data Warehouse.


texts: Get current-processed column information in Oracle datapump

texts:
In order to mask data I am using REMAP_DATA parameter in Oracle datapump as follows:

expdp \'/ as sysdba\' DIRECTORY=DUMP_DIR DUMPFILE=dumpfile.dmp LOGFILE=logfile.log SCHEMAS=user0 REMAP_DATA=user0.table1.col1:user0.pckg_mask.fun_mask REMAP_DATA=user0.table2.col2:user0.pckg_mask.fun_mask REMAP_DATA=user0.table1.col3:user0.pckg_mask.fun_mask

This is just an example. Instead of creating and deploying individual functions for different columns I am using fucnction overload method hence using the same function name but with different input parameter datatype.

But some columns need special logic for masking and the logic is stored in a  central table.

So, is it possible that I can know which column is being processed by datapump and from which column it will take export next?
(So that I can deploy the logic at run-time for that column which is being exported currently)

The question might be vague or complicated.
Please do ask for needed information or any suggestion for improving question quality.


texts: SQL Server 2008 - Restore Database With RESTART

texts:
I am trying to restore a database from a backup file on a SQL Server 2008 instance using the following SQL command:

BEGIN
    RESTORE DATABASE [MyDataBase]
    FROM DISK=@db_location
    WITH STATS=5,
    FILE=1,
    MOVE @db_OldName TO @db_NewFile,
    MOVE @db_OldLogName TO @db_NewLogFile,
    NOREWIND, NOUNLOAD, RESTART, RECOVERY
END

and it fails with the following error:

  Msg 3264, Level 16, State 1, Line 19
  The operation did not proceed far
  enough to allow RESTART. Reissue the
  statement without the RESTART
  qualifier. Msg 3013, Level 16, State
  1, Line 19 RESTORE DATABASE is
  terminating abnormally.

If I do not use the RESTART option the backup is successful. On SQL Server 2005 it works even with the RESTART parameter.

Since the SQL command is issued by a program I cannot change it. Is there a way to work around this issues on SQL Server 2008?


texts:
How to return "NaN" using SQRT function in Oracle SQL?  Here is my unsuccessful attempt:

SELECT SQRT(4) AS "SQRT of 4",
       SQRT(0) AS "SQRT of 0",
       SQRT(-1) AS "SQRT of -1"
FROM DUAL;

As I understand, a successful query might use the TO_BINARY_DOUBLE function but additional approaches are welcome.


texts: Why does Antelope (a sensor-network DBMS) do an inverse select in order to remove tuples?

texts:
Antelope (a database management system [DBMS for brevity] which enables a sensor to act as a database server) claims that its remove operation is like a swap between two tables. It does an inverse select of the tuples, assigns the result to a new relation (table), removes the old relation (table), and finally renames that new relation (table) with the older name.

  "The Remove Operation. Data tuples can be deleted through the REMOVE
  operation. This operation takes a condition of the same syntax as is
  used by SELECT, and removes all tuples matching the condition.
  Internally, Antelope translates this operation to a SELECT query, with
  an inverse condition, and assigns the result into a temporary
  relation. The old relation is then removed, and the temporary relation
  takes the name of the old relation. Hence, the cost of the REMOVE is
  comparable to that of a SELECT with an assignment, given an equal
  number of tuples to process." (A Database in every sensor)

I know that they use FLASH memory card on each sensor node and have a database on each sensor node. But why do they do an inverse select instead of removing everything they need once? Isn't that more costly?

Thanks for your answers.


texts:
I have a search page where users can select from multiple variables.  When the page is submitted to a package in the database a cursor is constructed and opened.

a snippet to show what I mean

PROCEDURE COMPLAINTQUERY(status_in IN NUMBER, userid IN NUMBER, many other parameters
     cursor_out                      OUT SYS_REFCURSOR)
   IS
v_query           CLOB;
BEGIN
--much processing to construct the query
            OPEN cursor_out FOR v_query
               USING casestatus_in,
                     userid_in,
                     --and so on....
                     ;
END COMPLAINTQUERY;

How could I save the user input in the query as text?  The problem is the dynamic substitution.  If the user entered 1 for casestatus_in and 390 for userid_in I would like to save the query with the substituted variable for reuse later
:

Select * from cases where status = 1 and userId = 390;

Edit: @Justin Cave Good point.  The query starts out using bind variables and to store it in a table for reuse will product a static query. I guess I want the best of both worlds.  I have a few users for this application who do a lot of querying so it would be a nice feature to save their input.  Maybe I should save the the bind variables.

Edit @Justin Cave If you were to suggest that I hold the user parameters in a collection I would accept that as an answer


texts:
I'd like to create a view from the query below, but the query contains a sub query which I need to apply a filter to (the place is marked with a comment). I'm wondering if it's possible somehow to adjust the query to have the subquery filtered by those params.

The query

select
 form_name,
 nvl(form_pravo,0) as form_pravo,
 nvl(fl_pravo,0) as menu_pravo,
 menu_nazov as menu_name
from(
SELECT
    form_name,
    pravo as form_pravo
  FROM (
    SELECT
        f.ID as form_id,
        f.NAME as form_name,
        f.XF_MODULY_ID as modul_id,
        u.ID as uziv_id,
        f.XF_MODULY_ID
          FROM xf_forms f, xf_uziv u) uf
       LEFT JOIN
        xp_forms_uzivs fu ON form_id  = fu.XF_FORM_ID AND uziv_id = fu.XF_UZIV_ID
            where modul_id = 14 and uziv_id = 1 -- <--HERE
            )
        full join (
            select
                SUBOR,
                FL_PRAVO,
                menu_nazov,
                uziv_id,
                modul_id
            from xv_uziv_menu
        ) um on form_name = um.subor
        where uziv_ID = 1 and modul_id = 14


texts: See other schema's tables in SqlDeveloper

texts:
When I connect to an Oracle DB with user XXX I see the tables, packages, etc. in schema XXX.
Is there a way so that I can connect with user XXX and see things in another schema YYY?

When I browse objects in SQLDeveloper I can see in the statement log that it sends XXX as parameter like "SCHEMA"="XXX", so I would just need to trick sqlDeveloper to use "SCHEMA"="YYY".


texts: How do I query for leaf nodes in a hierarchy using a closure table?

texts:
I have some hierarchical data (representing musical instruments and playing techniques), where each leaf in the hierarchy is an audio file.

The nesting depth of leaf nodes will vary because some playing techniques will have sub-articulations.

Example paths to leaf nodes could be

/philharmonia/trumpet/mute/muted_trumpet1.wav
/philharmonia/trumpet/legato/attack/legato_trumpet1.wav
/ircam/violin/pizz/violin_pizz1.wav

I have the data stored using a master table and closure table:

sqlite> PRAGMA table_info(as_node);
0|id|INTEGER|0||1
1|name|TEXT(1024)|1||0
2|file|INTEGER|0||0
3|parent|INTEGER|0||0

sqlite> PRAGMA table_info(as_node_closure);
0|id|INTEGER|0||1
1|ancestor|INTEGER|0||0
2|descendant|INTEGER|0||0
3|depth|INTEGER|0||0

How can I query the database to select all leaf nodes where an ancestor node matches a given string at given nesting level?

For example in plain English: "give me all leaf nodes where ancestor node is 'trumpet' at a nesting level of 2"

With the above data, this should return muted_trumpet1.wav and legato_trumpet1.wav.

UPDATE 1

Some example data:

sqlite> select * from as_node
1|BrassP||
2|4Horns||1
3|FT||2
4|DYN_02||3
5|RR_01||4
6|Mic_CL||5
7|4H_CL_DYN2_FT_01.wav|1|6
8|Mic_FM||5
9|4H_FM_DYN2_FT_01.wav|2|8
10|Mic_RM||5
11|4H_RM_DYN2_FT_01.wav|3|10
12|Mic_SUR||5
13|4H_SURR_DYN2_FT_01.wav|4|12
14|DYN_03||3
15|RR_01||14
16|Mic_CL||15
17|4H_CL_DYN3_FT_01.wav|5|16
...

sqlite> select * from as_node_closure limit 10;
1|1|1|0
2|1|2|1
3|2|2|0
4|1|3|2
5|2|3|1
6|3|3|0
7|1|4|3
8|2|4|2
9|3|4|1
10|4|4|0

UPDATE 2:

This gives me pretty much what I want, but I'm sure it it's not the right way:

SELECT name from as_node WHERE id IN (SELECT descendant FROM as_node_closure WHERE ancestor IN (SELECT id FROM as_node WHERE name = "trumpet") AND descendant IN (SELECT id FROM as_node WHERE name LIKE "%.wav"));

This uses the inner SELECT to get all of the leaf nodes and return only descendants in that list.


texts: Is normalizing further to index on INT FK rather than VARCHAR worth it?

texts:
I asked a question about whether or not I should normalize some static data and the general consensus was that it is not necessary since the data is unchanging, thus it is not redundant and will not likely result in update/deletion/insert anomalies.

However, is it worth normalizing further so that I can get an index on an INT type rather than VARCHAR? I'm not sure how most DBMS's do comparisons on strings but I would imagine that integers are faster than strings for comparisons.

At the same time if we are talking about a B+ tree index we are only talking about 3-4 comparisons for internal nodes and then however many records exist for the key which I am not even sure if comparisons occur at that level.


texts:
I just started to learn to optimize my queries and to analyze their query plans. I thought this query would generate nonclustered index seek + key lookup.

SELECT ct.*
FROM Person.ContactType AS ct
WHERE ct.Name LIKE 'Own%';

Instead it uses clustered index scan. I don't know why.

I'm working with AdventureWorks2012 database on SQL Server 2012 Express.
There is clustered index on ContactTypeId column and nonclustered index on Name column.
There is third column (ModifiedDate) that is not part of any index. This table contains only 20 rows.

I suspect that query optimizer decided to do clustered index scan because table has only 20 rows and maybe it is quicker to scan index then to do key lookup.


texts: How do I create a non localhost connection with MySQL?

texts:
I'm currently hosting a small website on my college domain where people can fill out a form and post it to database.  I'm trying to figure out how I can set up/edit my MySQL so that its not localhost.

This is the current setting:

http://i.imgur.com/WP0KIml.png

Obviously when people fill out the form right now it fails since my MySQL is on localhost.  I tried setting the host name to my IP but that didn't work as well.


texts: How to remove sysadmin from sa login in sql server

texts:
I have a requirement wherein I need to remove sysadmin server role from sa login.
I created 1 more user and gave the sysadmin role to the user.
When I uncheck sysadmin from sa login it throws an error.
Cannot use the special principal sa error 15405.
Can anyone have any idea how to do this?


texts:
I have a code

select CONVERT(NUMERIC,
  CASE ISNULL(FLEXFIELD4,'') WHEN '' THEN 0
  ELSE FLEXFIELD4 END )
FROM DEIT_INDENT_DTL_INBOUND_RXL WHERE IND_INDENT_NO='NEE15SR000003'

which is giving me error

  "Conversion failed when converting the nvarchar value '0.000000' to
  data type int."

i am unable to find the solution.

help me..

Thank you in Advance.


texts: Using Aggregate functions on tags influxDB

texts:
Is is possible to somehow calculate for example avg on tags? I need to do something like
Select mean(tag_1) from myMeasurement where currency = 'currency1'
However, mean requires field, not tag value. Is there some work around how to apply aggregate functions to tag values?
Thanks!


texts: How to model optional product delivery companies

texts:
I would like to model the following in my database. I have several ways to deliver a product. Each product can be sent by only some, or by every method of delivery.

In my current design every product has a table where I type available  methods of delivery, for example: ups;dhl;fedex.

Do you have a better solution?


texts: Why does InnoDB store all databases in one file?

texts:
It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by default).

I understand that InnoDB will map the location of data in the file by individual index files for tables, but I do not understand why it mixes all data in one file. And more importantly, why mix the data of all databases on the server?

An interesting feature of MyISAM is that one can copy/paste a database folder to another machine and then use the database (without a dump).


texts: enable replicaSet in mongoDB yaml conf file

texts:
I want to enable oplog on mongoDB and the recommended approach is to setup a standalone replica set. I tried following the instructions here...

https://docs.mongodb.com/manual/reference/configuration-options/#replication-options

...and added the following to /etc/mongod.conf

replication:
   oplogSizeMB: 1024
   replSetName: rs0

After restarting I tried initialising and checking the the status but see the following...

> rs.initiate()
{
        "ok" : 0,
        "errmsg" : "This node was not started with the replSet option",
        "code" : 76,
        "codeName" : "NoReplicationEnabled"
}

> rs.conf()
2017-08-11T18:08:51.336+0900 E QUERY    [thread1] Error: Could not retrieve replica set config: {
        "ok" : 0,
        "errmsg" : "not running with --replSet",
        "code" : 76,
        "codeName" : "NoReplicationEnabled"
}

The error states run with the --repSet option, but if I try the following the error still appears

sudo /usr/bin/mongod --config /etc/mongod.conf --replSet rs0

Update
The config is being passed in

2017-08-13T21:19:03.708+0900 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=63900M,session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),
2017-08-13T21:19:05.688+0900 I CONTROL  [initandlisten]
2017-08-13T21:19:05.689+0900 I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
2017-08-13T21:19:05.689+0900 I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
2017-08-13T21:19:05.689+0900 I CONTROL  [initandlisten]
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten]
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] ** WARNING: You are running on a NUMA machine.
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] **          We suggest launching mongod like this to avoid performance problems:
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] **              numactl --interleave=all mongod [other options]
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten]
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten]
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2017-08-13T21:19:05.690+0900 I CONTROL  [initandlisten]
2017-08-13T21:19:05.912+0900 I FTDC     [initandlisten] Initializing full-time diagnostic data capture with directory '/var/lib/mongodb/diagnostic.data'
2017-08-13T21:19:05.915+0900 I REPL     [initandlisten] Did not find local voted for document at startup.
2017-08-13T21:19:05.915+0900 I REPL     [initandlisten] Did not find local replica set configuration document at startup;  NoMatchingDocument: Did not find replica set configuration document in local.system.replset
2017-08-13T21:19:05.916+0900 I NETWORK  [thread1] waiting for connections on port 27117


texts: Reducing impact of SUM(DATALENGTH()) over linked server

texts:
I have a query that reports back to me how much time elapsed for records to be added. I also in this query track the size of an image that was added to the database. From what I can see it looks like it is returning the whole image from the linked server and then calculating the size. Is there an easy way I can get the calculation of size to occur on the other side of the link so that the return can be faster?

SELECT
    [Date] = CAST(DATEADD(DAY,-@DaysBack,GETDATE()) AS DATE)
    ,NOTES = N.RPMID
    ,TimeTaken = DATEDIFF("SECOND",SN.PickedUpDate,SN.ProcessedDate)
    ,PictureSize = SUM(CAST(DATALENGTH(SI.[Image])AS BIGINT))
    ,RecordsAdded = CAST(COUNT(*) AS BIGINT)
FROM
    DBServer1.DB1.dbo.Notes AS N WITH (NOLOCK)
    INNER JOIN DBServer1.DB1.dbo.NoteDistribution AS ND WITH (NOLOCK) ON N.Notes=ND.Notes
    INNER JOIN dbo.Submission_Notes AS SN WITH (NOLOCK) ON SN.RetailPlanGuid=N.RPMID
    LEFT JOIN DBServer1.DB1.dbo.SavedImage SI WITH (NOLOCK) ON SI.Notes=N.Notes
WHERE
    1=1
    AND N.RecordStampWhenAdd > CAST(GETDATE()-@DaysBack AS DATE)
    AND N.RecordStampWhenAdd < CAST(GETDATE()-@DaysBack+1 AS DATE)
    AND DATEDIFF("SECOND",SN.PickedUpDate,SN.ProcessedDate) > 0
GROUP BY
    N.RPMID
    ,DATEDIFF("SECOND",SN.PickedUpDate,SN.ProcessedDate)

The part in question is the picturesize that is returned. I want the calculations in there to be done on the server where the data is stored and just have the number return to me. Any help on this would be great. Thanks.


texts: SQL INSERT INTO SELECT query for updating on same table with some modification

texts:
I want to update this table
Table name: Student

Name      Department

Alex         CSE
Bob          EEE
Briyan       ME

by copying all rows with some modification like this table:
Table name: Student

Name      Department

Alex         CSE
Bob          EEE
Briyan       ME
Mr. Alex     SCIENCE(CSE)
Mr. Bob      SCIENCE(EEE)
Mr. Briyan   SCIENCE(ME)

What should be the SQL query ... can you help please?
I have tried this far:
INSERT INTO
    Student (Name, Department)
SELECT Name,Department FROM Student;


texts:
I currently have a query that works on two tables of expense and income. This is an example of what the structure of both tables looks like since they have the same columns:

|       id|        date |   amount|
|---------|-------------|---------|
|        1|  2019-02-02 |     2500|
|        2|  2019-03-16 |    4000 |
|        3|  2019-04-02 |    5430 |

and this is the query I currently have:

SELECT
    t1.month,
    COALESCE(t2.amount, 0) AS expenses,
    COALESCE(t3.amount, 0) AS incomes
FROM
(
    SELECT 1 AS month UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12
) t1
LEFT JOIN
(
    SELECT MONTH(date) AS month, SUM(amount) AS amount
    FROM expenses
    GROUP BY MONTH(date)
) t2
    ON t1.month = t2.month
LEFT JOIN
(
    SELECT MONTH(date) AS month, SUM(amount) AS amount
    FROM incomes
    GROUP BY MONTH(date)
) t3
    ON t1.month = t3.month
ORDER BY
    t1.month;

Here is the thread to see in detail and perform tests:http://sqlfiddle.com/#!9/466bd69/1

The query returns the total amount of both tables for each month of the current year, this works well. However, when reviewing the execution plan since I have many records, it takes too long to go through all the records of both tables, so how can I optimize it? Try adding an index to the date field without any improvement. Should I restructure my query?


texts: Can PostgreSQL tell me what configuration files it's using?

texts: I am having trouble with some changes I'm making to a configuration file. I think PostgreSQL is not using the ones I'm modifying. Is there any way to get postgres (maybe some environment variable) to tell me where I can find the various config files, including pg_hba.conf and pg_ident.conf?


texts: PostgreSQL batch inserts fail silent randomly

texts:
I have been using Kettle to import data from a SQL Server to PostgreSQL on AWS RDS and when I was enabling all of my foreign key constraints I realized that data was missing, often in the even hundreds.

But checking the log of the Kettle job, it seems to be doing all read and writes correctly from a table of 1923 entries

2017/02/26 21:36:42 - Table output.5 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.3 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.7 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.6 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.1 - Finished processing (I=0, O=241, R=241, W=241, U=0, E=0)
2017/02/26 21:36:42 - Table output.2 - Finished processing (I=0, O=241, R=241, W=241, U=0, E=0)
2017/02/26 21:36:42 - Table output.4 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.0 - Finished processing (I=0, O=241, R=241, W=241, U=0, E=0)

But when running the query count on Postgres I get 1423 entries, and similar behavior on maybe 10% of all tables I have transferred.

As I don't have any prior experience in neither Kettle nor Postgres, what is it that can cause this issue?


texts: How to copy a PostgreSQL database at a filesystem level between servers

texts:
I have a large PostgreSQL database on a server with a failing disk. I cannot run pg_dump because of disk errors.

I have another server with the identical OS (Ubuntu 16.04) and the identical version of PostgreSQL (9.5.6). How do I copy the raw database files to the new server? I don't care if read errors corrupt parts of the database, as I have ways of identifying and fixes those issues, but I need the data copied over before I can fix it. From what I can tell, there aren't many read errors, but even one will stop pg_dump.

There's a scant wiki page on this topic, and it suggests doing a "filesystem level copy" but makes no mention of how to actually do this.

Edit: I used rsync to copy over my data directory (/var/lib/postgresql/9.5/main) and configuration directory (/etc/postgresql/9.5/main). Running sudo service postgresql start on the new server runs without error, but trying to connect via sudo -u postgres psql returns:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

and the log shows:

2017-05-23 15:13:08 EDT [14604-1] [unknown]@[unknown] LOG:  incomplete startup packet
2017-05-23 15:13:08 EDT [14603-2] LOG:  MultiXact member wraparound protections are now enabled
2017-05-23 15:13:08 EDT [14602-1] LOG:  database system is ready to accept connections
2017-05-23 15:13:08 EDT [14608-1] LOG:  autovacuum launcher started
2017-05-23 15:13:43 EDT [14602-2] LOG:  received fast shutdown request
2017-05-23 15:13:43 EDT [14602-3] LOG:  aborting any active transactions
2017-05-23 15:13:43 EDT [14608-2] LOG:  autovacuum launcher shutting down
2017-05-23 15:13:43 EDT [14605-1] LOG:  shutting down
2017-05-23 15:13:45 EDT [14605-2] LOG:  database system is shut down
2017-05-23 21:33:29 EDT [27345-1] FATAL:  could not load server certificate file "/etc/ssl/certs/ssl-cert-snakeoil.pem": No such file or directory

What is this missing certificate and how do I fix it?

Edit: I've fixed the SSL error by following these instructions, and now all my files seem to have been transferred.

My database seems to be largely accessible. The only issue I've found is when I go to do a full vacuum, I get the error:

ERROR:  could not open file "base/106800/107273": No such file or directory

How do I fix this? I realize it's file corruption, probably caused by that file not being readable during the rsync. How do I "fill it in" with blank values so the vacuum can proceed? The error occurs when vacuuming a specific table, and the data in that table can be regenerated.


texts: How can I use user defined variables and `USE`?

texts:
I have some schemas, all with the same format. The format is: schema name, underscore, single digit (example: database_1).
How can I loop through all of these schemas easily? I tried SET @num = 1; USE database_@num; But it says

  Unknown database 'database_@num'

I'm using MariaDB 10.1.30


texts: SQLite - get every row that has a common entry in the second column

texts:
I am working on an Android studios project and I have a table that has two columns item and listname.

I want to get all the items that have a common Listname so in this case if I were looking for items that have Market as a listname I only want to get the last 4 from the table, I only want to return the items. I have this but it keeps crashing.
   public Cursor getItem(String listname) {
    SQLiteDatabase db = this.getWritableDatabase();

    String selectQuery = "SELECT  * FROM " + LIST_ITEM_TABLE + " WHERE "
            + ItemListName + " = " + listname;

    Cursor c = db.rawQuery(selectQuery, null);

    return c;
}


texts:
Given the following table structure...

Orders
======
OrderId int PK

OrderEvent
===========
OrderEventId int PK
OrderId int FK
EventTypeId int FK

I've got multiple concurrent processes SELECT-ing orders but each order should only be read once overall. So upon an order being retrieved I need to add a row in the OrderEvent table to mark that an order has been read (EventTypeId = 1).

So my idea is to have the following query for the read...

SELECT *
FROM Orders o WITH (UPDLOCK)
WHERE NOT EXISTS (SELECT *
                  FROM OrderEvent oe
                  WHERE oe.OrderId = o.OrderID
                    AND EventTypeId = 1)

This runs within a transaction and after the read the order event row is added to mark the order as having been read.

So the question is, will this guarantee that each order can only be retrieved once?

What I'm not sure about is when the lock is put on the order row? Is it before or after the where clause has been evaluated?


texts: Enterprise Manager Cloud Control 13c - Email notification should send only once

texts:
I have an alert message with tablespace usage that should recheck the status in Cloud Control 13c and send the email only once, but it sends every 30 minutes. It shows me the TS_USAGE error of the tablespaces when it grows over 95%.
Can I have something configured to send only once when it happens and then some kind of clear notifications to send back that everything is ok, when I resize manually the tablespace and fix the issue?
Kind regards.


texts: Is offsite replication from MySQL AWS RDS with encrypted RDS storage possible? How?

texts:
AWS RDS for MySQL now supports encrypted storage backends.  This encrypts the MySQL data and binlogs with a key within AWS's key management system, thus causing offsite replication issues because we do not have the binlog key on our private replica.  We know that you can not replicate encrypted->unencrypted and unencrypted->encrypted, so the question is this:

Can we provide encryption keys to AWS for the RDS storage encryption such that we can use that key in our remote replication (offsite, not in AWS) to replicate properly?
If so, what is the procedure for generating a key and uploading it AWS?

(We don't have an AWS support agreement, so if you do, perhaps you could ask on our behalf to answer this question.)
Thanks for your help!


texts: Promoting warm standby to master with Postgresql 9.4 using streaming replication

texts:
I've currently got 2 database servers running Centos 6 and Postgresql 9.4 (identical clones on the same network). They are set up as a primary and a backup created using pg_basebackup from the primary, with the primary copying WAL to the secondary's file system and the backup set to update from the WAL and connect to the primary using streaming replication.

This all works fine up to the point that I kill the master and try to promote the backup to be the new master. I'm running kill -9 to simulate a catastrophic failure of the master and then touching the trigger file on the backup shortly afterwards.

The problem is that as soon as I touch the trigger file on the backup server it detects it and then fails to start up as the primary.

Here are the logs from the backup at the point I disconnect it from the primary:

< 2015-10-05 14:20:52.888 BST >LOG:  trigger file found: /tmp/psql.trigger
< 2015-10-05 14:20:52.888 BST >LOG:  redo is not required
< 2015-10-05 14:20:52.904 BST >LOG:  restored log file "000000010000000000000004" from archive
< 2015-10-05 14:20:52.906 BST >PANIC:  record with zero length at 0/40000C8
< 2015-10-05 14:20:52.906 BST >LOG:  startup process (PID 3101) was terminated by signal 6: Aborted
< 2015-10-05 14:20:52.906 BST >LOG:  terminating any other active server processes
< 2015-10-05 14:21:08.580 BST >LOG:  database system was interrupted while in recovery at log time 2015-10-05 13:50:08 BST
< 2015-10-05 14:21:08.580 BST >HINT:  If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
< 2015-10-05 14:21:08.935 BST >LOG:  entering standby mode
< 2015-10-05 14:21:08.947 BST >LOG:  restored log file "000000010000000000000004" from archive
< 2015-10-05 14:21:08.949 BST >LOG:  record with zero length at 0/40000C8
< 2015-10-05 14:21:08.951 BST >FATAL:  could not connect to the primary server: could not connect to server: Connection refused
        Is the server running on host "192.168.56.101" and accepting
        TCP/IP connections on port 5432?

The logs at 13:50 were just the server starting up in recovery mode.

The config in postgresql.conf on the primary is:

listen_addresses = '*'
wal_level = archive
max_wal_senders = 2
archive_mode = on
archive_command = 'rsync -a %p postgres@192.168.56.102:/apps/pgsql/9.4/write_ahead_logs/%f'
archive_timeout = 600

The config in recovery.conf on the backup is:

standby_mode = 'on'
primary_conninfo = 'host=192.168.56.101 port=5432 user=backups password=abc'
restore_command = 'cp /apps/pgsql/9.4/write_ahead_logs/%f %p'
archive_cleanup_command = 'pg_archivecleanup /apps/pgsql/9.4/write_ahead_logs %r'
trigger_file = '/tmp/psql.trigger'

I'm sure there's something important I'm missing but after searching the internet and the docs I can't put my finger on it. Any help would be much appreciated.


texts: Where are connections to databases made from when using SSRS?

texts:
If I have SSRS set up in the following format

Server 1 : Running SQL Server, hosting the SSRS database

Server 2 : Running SSRS, configured to store metadata on Server 1 database

And SSRS reports are configured to connect to say, Server 3

Will the connection to server 3 take place from server 1 or server 2?


texts: Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

texts:
I had to write a simple query where I go looking for people's name that start with a B or a D :

SELECT s.name
FROM spelers s
WHERE s.name LIKE 'B%' OR s.name LIKE 'D%'
ORDER BY 1

I was wondering if there is a way to rewrite this to become more performant. So I can avoid or and / or like?


texts: GROUP BY rewrite to subqueries with easily interchangeable AND OR conditions

texts:
Tables:

content        tag_map       extrainfo
|id|title|    |id|tagid|    |id|lat|lng|
|--|-----|    |--|-----|    |--|---|---|
|1 |Lorem|    |1 |8    |    |3 |0,4|0,1|
|2 |Ipsum|    |2 |9    |    |2 |1,1|1,2|
|3 |Dolor|    |1 |9    |
              |1 |7    |

id is unique index

SELECT * FROM content, tag_map, extrainfo
WHERE
tag_map.id = content.id
AND
extrainfo.id = content.id

#AND OR conditions here
#conditions can be very different but I would like
#to use the same query structure
#just change conditions here as necessary

GROUP BY content.id

this query works - but slow.
I found the most alternatives involve subqueries, but I can`t figure out a way to use subqueries and still be able to freely change AND OR conditions that involve columns from ALL tables.

Other non-subquery methods are welcome too.


texts: Are scripts like sp_BlitzIndex and the others accurate on restored backups?

texts:
I am a developer trying to get a better grasp of things.  I am wondering if using the scripts from BrentOzar.com will work on restored backups?  For example if I have the DBA give a backup of production, then restore that on my dev box, then run the scripts, will I get accurate information or is vital information lost during that process?

I am guessing that the answer is both yes and no.  It looks like a good bit of the information that sp_BlitzIndex provides will work regardless since it seems to be based on the structure of the database.  I am not so sure about sp_BlitzCache, etc.

I guess what I am asking is what types of things in these scripts won't work when running them on a restored backup and just as important, why not?

Thanks.


texts:
The problem:
I am running on a Webhotel a MySql database with different schemas with a maximum size limit on each schema. I can't change any settings on the DBMS. To be able to keep the schema below the max size i can remove the oldest rows to make space for newer ones, like a LIFO.

The only way to do this I thought was making a trigger. I'm trying to make one like this:

CREATE TRIGGER inventoryproposalprocessed_AINS AFTER INSERT ON iventoryproposalprocessed
    FOR EACH ROW
    BEGIN
      DECLARE size INTEGER;
      SELECT sum( data_length + index_length ) / 1024 / 1024 INTO size FROM information_schema.TABLES where table_schema like 'd40013_x_frodo' ;
      IF size > 99 THEN
         DELETE FROM inventoryproposalprocessed order by _id limit 25;
      END IF;
    END;

But it doesn't work. How should a trigger like that look like?


texts: Does PostgreSQL always sequentially scan pages in the same order?

texts:
Yet another question from my discovery of the great new EXPLAIN options in PostgreSQL. This one focuses on the BUFFERS option.
Here is EXPLAIN:
EXPLAIN (ANALYZE, BUFFERS) SELECT event_time FROM ui_events_v2  WHERE page ~ 'foo' LIMIT 1;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..1539.68 rows=1 width=8) (actual time=0.858..0.858 rows=1 loops=1)
   Buffers: shared read=10
   I/O Timings: read=0.735
   ->  Seq Scan on ui_events_v2  (cost=0.00..3313394.58 rows=2152 width=8) (actual time=0.857..0.857 rows=1 loops=1)
         Filter: (page ~ 'foo'::text)
         Rows Removed by Filter: 112
         Buffers: shared read=10
         I/O Timings: read=0.735
 Planning Time: 6.455 ms
 Execution Time: 0.877 ms

It is quite fast - and this query is very slow on a cold start. This is a 30M row table, and seven rows have page containing substring foo. The first row matching this query would seem to show up 604k pages in, which would be ~20% of the ~3M pages mentioned in pg_class:
SELECT min(ctid) FROM ui_events_v2 WHERE page ~ 'foo';
     min
-------------
 (604435,10)

My assumption was that even if every page of this table were in the PostgreSQL or operating system cache, it still needs to walk some linear list of pages for a sequential scan. The section Buffers: shared read=10, and the 0.877 ms execution time suggests to me that it's somehow "picking up where it left off" and starting at pages that are far from the beginning. I thought it moved through page IDs and peeked into a cache trying for each page as it moved, but does it perhaps start instead from the cache itself? If that were the case, how does it discover the non-cached pages?
I know that rows aren't guaranteed to be found in any particular order, but I figured that within the same query strategy, the path followed would be relatively similar.


texts: sudo service mongod start returns error 100

texts:
I'm encountering the following error upon executing sudo service mongod start in terminal.

    mongod.service - MongoDB Database Server
   Loaded: loaded (/lib/systemd/system/mongod.service; disabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Thu 2018-10-18 12:43:22 ACDT; 7s ago
     Docs: https://docs.mongodb.org/manual
  Process: 3155 ExecStart=/usr/bin/mongod --config /etc/mongod.conf (code=exited, status=100)
 Main PID: 3155 (code=exited, status=100)
      CPU: 22ms

Oct 18 12:43:22 david- systemd[1]: Started MongoDB Database Server.
Oct 18 12:43:22 david- mongod[3155]: 2018-10-18T12:43:22.736+1030 I CONTROL  [main] Automatically disabling TLS 1.0, to force-enable TLS 1.0 specify --sslDisabledProtocols 'none'
Oct 18 12:43:22 david- systemd[1]: mongod.service: Main process exited, code=exited, status=100/n/a
Oct 18 12:43:22 david- systemd[1]: mongod.service: Unit entered failed state.
Oct 18 12:43:22 david- systemd[1]: mongod.service: Failed with result 'exit-code'.

I am unfamiliar with this error, could anyone please suggest what the fix might be?


texts: If you schedule a TSQL Job in SQL Server 2008 R2 and additionally need to copy files with it, is it better just to use a script?

texts:
I noticed that alot of backup jobs are scheduled using SQL Server 2008 R2, but we have a system here at work that also requires the backup of a directory in addition to the database contents.  I've written a batch file that calls sqlcmd and calls a TSQL script to backup the database; the script also includes copying the directory and logging the result of the operations.

(And yes, I probably should have used powershell instead...)

I would use the SQL Server Job functionality as it has email notification of failures, event loggins and other perks that my batch file does not; but I don't see a way to work copying the directory into that in a all or nothing approach; so is it possible to work copying a directory into an SQL Server Agent Job?


texts: SQL Server Maximum and Minimum memory configuration

texts:
What are the correct settings for min-memory and max-memory in this use case?

The server has 8GB ram, dual Intel Xeon processors, running Windows Server 2008 R2 / Sql Server 2008 Standard Edition. It's running several databases ranging from 30GB - 5GB in size.

Originally the memory usage was set to the default settings (min=0 max=2,147,483,647). On these settings most of the memory usage was taken up by sqlservr.exe and the server would eventually need to be restarted every day or two. It would run normally at first but within a day start to timeout on simple operations like looking up a record using the primary key.

I have changed min=4,096 and max=6,144. This results in only 1.4GB memory usage. However now all four cpus are running at 50-60% cpu usage constantly. Tasks are taking roughly 1/3rd longer to execute, although the server is much more stable.


texts: How is my software conecting on my SQL Server if it failover via availability groups?

texts:
I'm a Little confused about this topic.

I know with a default instance, we can create a cluster with let's say, with the ip  x.x.x.10. then automatically this IP will connect to the x.x.x.9 that is the principal. if the principal is down, it will connect to x.x.x.8 that is the secondary.

How does this work if I have a named instance? I'm having a problem that I create both instances X.X.X.36\SQLPRIMARY and X.X.X.37\SQLBACKUP. I'm trying to connect via SSMS to the X.X.X.38 That is the CLUSTERs Ip but It shows the error that the instance cannot be found. Normally with a non named instance, using that cluster IP, it points to the primary SLQ instance on that cluster.

Does the listener have this job here? I think I quiet not understand it's purpose.


texts: Common `event` (datetime) table for say a manufacturing DB, an anti pattern?

texts:
There are many different types of events that can happen during a manufacturing process.
E.g.

Built Step1
Build Step2
Test
Checked into repairs
Checkout out of repairs
Packaged
Dspatched, etc.

I see two ways to model each step:

Have separate independant tables. Each table with its own timestamp field.
Extract the timestamp field from the tables, and place it in a common event tables which has say 2 fields:

timestamp field
A column which links it to which type of event it is (which table has the specific details for that type of event).
Could also have a user field to track who did what etc.

Any reasons why not to use way 2 (event table)? It seems advantages for running reports on what events happened during a certain time frame. A way of uniting events in the factory. But my research seems to always point towards avoiding any common function tables.


texts:
I have a table in BigQuery with Xday, Xmonth, and Xyear columns but the data was user generated and apparently done without adequate input validation so some of it is nonsense.  It's easy enough to do things like Xday between 1 and 31, but I still get an error when doing date(Xyear, Xmonth, Xday) if for example someone put in February 30th.
Is there some way I can either validate the date prior to doing the conversion to the date type or a way to just toss out the rows causing errors instead of throwing the exception?


texts: Normalized database table with non-atomic values

texts: I'm creating a normalized table for a questionnaire that the customers fill in and in one of the columns is a field called "extra comments". I realize that with the "extra comments" field, the data will not be atomic as the customers may be writing sentences or can the sentences be considered as atomic values? How do I keep the "extra comments" field and keep the table normalized?


texts: "Host has low open file limits" error in MMS

texts:
I am running Standalone MongoDB (v 2.6.0 64-bit) host on Centos (v 6.5). Centos is guest OS on Windows 7 host running under VirtualBox (4.3.10). MongoDB process runs under root  account. And it is single mongod process on that box.

I am monitoring this instance using MMS Service. MMS shows this warning/error:

  "Host has low open file limits".

MongoDB documentation says that the following are recommended setting:

-f (file size): unlimited
-t (cpu time): unlimited
-v (virtual memory): unlimited
-n (open files): 64000
-m (memory size): unlimited
-u (processes/threads): 64000

I did the following in Centos:

Created 99-mongodb-nproc.conf file as proscribed in /etc/security/limits.d with these settings:

root       soft    nproc     64000
root       hard    nproc     unlimited
In /etc/security/limits.config file specified this:

root soft nofile 64000
root hard nofile 128000
In /etc/sysctl.conf file specified this:

fs.file-max = 512000

After reboot I checked

ulimit -a

And I am able to match limit setting above precisely but "Host has low open file limits" error is still there in MMS; it just would not go away no matter what I do.

If it is of any consequence I have 16GB memory on Host PC and I allocate 14GB to this guest Centos OS.

What am I still missing?


texts:
I would like to estimate the log growth consumption for a database with full recovery model  for a particular transaction/group of transactions which is filling up my log drive. I would like to forcast this before a transaction is run.Is there a way to find this out in SQL Server(any version)
Thanks in advance.


texts: Postgres foreign key with ON DELETE SET NULL for only some of its columns

texts:
I have added a (new) 2nd foreign key to a table a. Its 1st foreign key is optional, hence the constraint ON DELETE SET NULL on the foreign key. The 2nd foreign key is mandatory, hence the (new) constraints NOT NULL on a1 and a2 and (new) the constraint ON DELETE CASCADE on the foreign key. The 2nd foreign key also consists of a subset of the columns for the 1st foreign key.

CREATE TABLE a
(
  a0 INTEGER NOT NULL PRIMARY KEY,
  -- other columns
  a1 INT NOT NULL,
  a2 INT NOT NULL,
  a3 INT,
  a4 INT,
  FOREIGN KEY (a1, a2)         REFERENCES b ON DELETE CASCADE,
  FOREIGN KEY (a1, a2, a3, a4) REFERENCES c ON DELETE SET NULL
);

The problem that I run into is that I can now no longer DELETE FROM c, since this invalidates the NOT NULL constraints on a1 and a2. What I would like to happen in the case of such deletes is for a1, a2 to remain unmodified and for a3, a4 to be set to NULL. Is there a concise syntax (more concise than with an extra DELETE trigger) for defining a constraint that achieves this?


texts: Cannot remotely access PostgreSQL on EC2 instance

texts:
I was unable to access a remote AWS postgres database via things
like psql -h *EC2ExternalIP* -U *me*.  I would get:

psql -h *EC2ExternalIP* -U *me*
psql: could not connect to server: Operation timed out
Is the server running on host *EC2ExternalIP* and accepting
TCP/IP connections on port 5432?

same thing trying a simple telnet connection to this port:

curl telnet://*EC2ExternalIP*:5434
curl: (7) Failed to connect to *EC2ExternalIP* port 5434:
Operation timed out

and even though i could access other services (eg, apache) on this
same EC2ExternalIP.

So I poked around at threads like these:

How to connect to an remote PostgreSQL database on Ubuntu using pgAdmin3?
Connect to remote EC2 Postgresql database
https://askubuntu.com/questions/423165/remotely-access-postgresql-database/827358#827358
https://stackoverflow.com/questions/17770329/cannot-connect-to-postgresql-remotely-on-amazon-ec2-instance-using-pgadmin

and they helped me to ensure I'd made appropriate modifications to
postgresql.conf and pg_hba.conf; the right settings for you will
depend on your instance's security concerns.  But it is still not
working.

What should I do to make this DB accessible?


texts: Greatest-n-per-group in a one-to-many relationship


texts:
There is a table with different sets a(1,2), b(2,3,4) and etc.

-- table1 --
set_name | value
a        | 1
a        | 2
b        | 2
b        | 3
b        | 4
c        | 3
c        | 5
...      | ...

How to get a table containing name of set with values which is unique for all table? Result should be like this:

-- table2 --
set_name | value
a        | 1
b        | 4
c        | 5
...      | ...

Since value 2 is in set a and set b, it's excluded; since value 3 is in sets b and c, it's excluded....

In other words, I want to see the rows where the value is listed for one and only one set_name.


texts: How to keep history for a one-to-many relationship?

texts:
I have a simple one-to-many (1:M) relationship but I need to keep a history for the changes.

For example, a task can have many employees, but each employee can only have one task at a time.

I need to keep a history for the task assignments in order to know the past tasks that were assigned to each employee.

I know that I can convert it to a many-to-many (M:N) relationship and add a “flag” column, but this will not keep the relationship constraint that stipulates that only one task can be assigned.

I'm pretty sure that there is a design pattern or best practice for the case but I can't find it. I'm using Oracle DBMS.

Employee Columns:

ID
Task_ID  (FK_Task_ID)
Employee Name
Other Employee Data

Task Columns:

ID  (PK)
Task Details

This is a direct (1:M) relation. The required is what if I want to keep log for all the tasks assigned for a certain Employee? The trivial solution would be make a many to many relation and adding intermediate table as follows:

Employee_Task Columns:

ID
Employee_ID (FK_Employee_ID)
Task_ID (FK_Task_ID)

This will remove the constraint that each employee should have only one assigned task at a time.


texts:
The task: What ingredients has the maximum amount of content in Pancake?

The tables:
Recipe (Name, group)
Ingredient (Iname, price)
Composition (Name, Iname, Amount)

I tried this query:

Select Iname, Amount
From Composition
Where Name='Pancake'
Ordered by Amount ASC

But it does not return the correct result. What's wrong with it?


texts: Whats the difference between information_schema.user_privileges and mysql.user?

texts: I have the notion that privileges set in mysql.user should reflect in information_schema.user_privileges but when I try to alter some privileges in mysql.user and queried information_schema.user_privileges they did not match? Am i understanding it wrong?


texts: Fastest way to check if record is related to a particular user in postgres

texts:
While implementing a sharelikes table I added a procedure to add a new record for a like.

I wanted to add a check before insert that the item being liked does not belong to the users who submitted the like request. Given function parameters Pshareid and Puserid I used the following conditional in the procedure

SELECT INTO checkuser true FROM ks.shares
WHERE id = Pshareid AND userid = Puserid limit 1;
IF checkuser THEN
  -- Cannot like your own share
ELSE
    --INSERT the new record
END IF;

In the select described above the record will almost always not exist.

Is that more efficient than a SELECT on the share id to return the record (if it exists which is very likely at the point of select) and then to check the userid of the returned record?

SELECT userid INTO checkuser FROM ks.shares
WHERE id = Pshareid limit 1;
IF checkuser = Puserid THEN
    -- Cannot like your own share
ELSE
    --INSERT the new record
END IF;


texts: Searching across collections based on different column name

texts:
I have following collections with fields in my mongodb database:
Collection: Category
[
    {
        "cat_id": "cat_id_1",
        "cat_name": "education"
    },
    {
        "cat_id": "cat_id_2",
        "cat_name": "Sports"
    }
]

Collection: Keywords
[
        {
            "key_id": "key_id_1",
            "key_name": "social"
        },
        {
            "key_id": "key_id_2",
            "key_name": "places"
        }
    ]

Collection: Artists
[
    {
        "art_id": "art_id_1",
        "artist_name": "Brian Adams"
    },
    {
        "art_id": "art_id_1",
        "artist_name": "ACDC"
    }
]

Collection: Episodes
[
    {
        "ep_id": "ep_id_1",
        "ep_title": "Episode Title 1",
        "categories": ["cat_id_1", "cat_id_2"],
        "keywords": ["key_id_1"],
        "program_id": "pr_id_1",
        "artists": ["art_id_1", "art_id_2"]
    }
]

Collection: Programs
[
    {
        "program_id": "pr_id_1",
        "pr_title": "Program Title 1",
        "categories": ["cat_id_1", "cat_id_2"],
        "keywords": ["key_id_1"],
        "artists": ["art_id_1", "art_id_2"]
    }
]

Now I need to write a query which will list programs based on search string matching Category, Artist, keywords, Episodes Title or Program Title. It would have been easy if it's a single collection by just using find and $regex and supplying searched string to it. However I failed to apply the same for this condition.


texts: How to clean up tables that don't belong into mysql/mysql database?

texts:
It seems that a *.sql file I recently imported had use mysql instead of use dbname. Now the mysql database contains a large number of tables which don't belong there.

Is there an easy/efficient way to clean this up?

Note: I am using MariaDB, but I think a generic answer would be more useful for others.

mysql  Ver 15.1 Distrib 10.1.10-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2


texts:
I'm trying to derive an Entity Relation diagram from a class diagram.
In the class diagram, I have a class Player with one to many relationship to another class Payment. In Payment there is no attribute 'paymentID' and 'playerID' is foreign key. Since the same player ('playerID') can make the same payment many times, I thought of adding an attribute 'paymentID' to the Payment table. Is this right? Will I still follow the requirements? I'm new to all these, thank you for any help.


texts:
We need to upgrade mariadb to latest version on critical prod servers.
We are planning to take backup and restore on the new server.
What type of backup is recommended for it? physical or logical?


texts: Create a SQL Server Redirect (Alias / Linked Server)

texts:
I currently have a database on a named SQL server instance called \Mobius and database name of Mobius

We need to move the database to the default (parent) instance but don't necessarily want to update our connection strings in all our applications (initially) so that a call to \Mobius will point to having the database of the same name.

Is there a way this can be done?


texts: SQL Server Troubleshooting Replication and Long-Running fn_dblog query

texts:
I am trying to get (pull) Transactional Replication working again for a subscriber.

Currently, in Replication Monitor I am seeing messages in the Publisher to Distributor tab and the Distributor to Subscriber tab.

In the distributor to subscriber history, I see 'concurrent snapshot for publication 'XX' not available'. In the publisher to distributor history, I see the Log Reader Agent initializing and then waiting for a response from the server (every 5 minutes the message is logged again).

Ultimately the execution of sp_replcmds fails.

I think there may be many unreplicated transactions which are causing the Log Reader Agent to hang. Following Troubleshooting LogReader Timeouts

I ran dbcc opentran and then attempted to run

select  [Transaction ID], count([Transaction ID]) as 'Count log entries'
from::fn_dblog('42006:31381:2',null)
where [Description] = 'REPLICATE'
group by [Transaction ID]

This query did not return any results after over 40 minutes. Moreover, when I just run SELECT count(*) FROM ::fn_dblog(NULL, NULL) I am not getting results after a long time (20 minutes).

I am starting to think there is something up with the size of my transaction log, or some kind of corruption, or something.

Any help would be greatly appreciated.


texts: Downgrading SQL Server 2012 to Lower Edition

texts:
I have a Win2008 R2 server with an enterprise edition of SQL Server 2012 installed. Because my production server is going to be standard edition, is there a way to downgrade editions from enterprise to standard? I have databases and users already configured in the enterprise instance, and analysis and native reporting service installed and configured.

Is this downgrade possible, or am I looking at a total re-install? Is there anything special I would need to do with my databases other than detaching prior to un-install and then attaching in the standard instance?


texts: Why do composite foreign keys need a separate unique constraint?

texts:
Here is a simple table where records may reference parent records in the same table:

CREATE TABLE foo (
    id         SERIAL  PRIMARY KEY,
    parent_id  INT     NULL,
    num        INT     NOT NULL,
    txt        TEXT    NULL,
    FOREIGN KEY (parent_id) REFERENCES foo(id)
);

With the added requirement that one of the other field values (num) must be identical between parent and child records, I thought a composite foreign key should do the trick. I changed the last line to

    FOREIGN KEY (parent_id, num) REFERENCES foo(id, num)

and got ERROR:  there is no unique constraint matching given keys for referenced table "foo".

I can easily add this constraint, but I don't understand why it is necessary, when one of the referenced columns (id) is already guaranteed to be unique? The way I see it, the new constraint would be redundant.


texts:
here is structure of both tables

category table
CREATE TABLE category (
`catID` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(60) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`catID`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

and the second table projects

CREATE TABLE `projects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`catID` int(11) NOT NULL,
`industryID` int(11) NOT NULL,
 `dsID` int(11) NOT NULL,
`countryID` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `catID` (`catID`),
KEY `industryID` (`industryID`),
KEY `dsID` (`dsID`),
KEY `countryID` (`countryID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now to add FK to tables here is the query but i keep getting the error

Can't create table .... (errno: 150)
ALTER TABLE `projects`  ADD CONSTRAINT FK_catID FOREIGN KEY projects (catID)  REFERENCES category (catID)


texts: Replace or Update a Table Row - based on the presence of a matching row item

texts:
Unfortunately I know next to nothing about php coding that contains some sql code. The examples I have seen online do not seem to match the same syntax that I inherited with this code, which is making this task confusing for me.
I have a database table called "effort" that contains two columns:
Column 1 = ad

Column 2 = theEffort

This line of code below works, and it will populate the table with the proper values for both the "ad" and "theEffort"
$sql = "INSERT INTO effort SET ad='$ad', theEffort='$theEffort'";

But I do not want the table to grow over time and contain redundant rows pertaining to the same ad. Each "ad" in this table should be unique. But each "ad" could have 5 different effort values, e.g., "theEffort" can be "easy", "medium", "difficult", "impossible".
The "ad" is 13 digit number, i.e. SFAD_000001234
Does anybody know how to modify my code above using similar syntax so that it would first check to see if the "ad" exists in the database table called "effort". And if so, instead of entering a new row it would update "theEffort" to the new value. And if the "ad" did not exist in any table row within the "effort" database then it would execute the INSERT INTO function, i.e., insert both the "ad" and "theEffort" as a new table row.
Thank you in advance,
-Jeff


texts: What's the differences between the two version of oracle client download file, "client" and "client home"?

texts:
I noticed the Oracle database download page has two groups of clients for users to download. There is a thing called "client" and has 32-bit version and 64-bit version. Also, there is a thing called "client home", which also has both 32-bit and 64-bit version. What are the differences between them? When should I use which?

Update

The download page URL is https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle19c-linux-5462157.html


texts:
We're using Trac for issue tracking. I migrated it from MySQL to PostgreSQL, but some of the ticket queries no longer work.

In the below, all I did this far is replace IFNULL() with COALESCE() and UNIX_TIMESTAMP() with TO_TIMESTAMP()::TIMESTAMP

I don't know what the error means. The query worked just fine in MySQL, and I'm not sure, after looking at pages discussing differences between it and PostgreSQL, what the problem is (I also don't know what to replace the SEC_TO_TIME line with, but removing that line doesn't fix the error).

SELECT IFNULL(CONCAT('Component ', t.component), 'Total for all Components') AS __group__,
    (CASE
      WHEN t.id > 0 THEN (CASE t.status WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' END)
      ELSE 'font-weight: bold'
    END) AS __style__,
    t.id AS ticket,
    IF(t.id > 0, t.summary, 'Total') AS summary,
    SEC_TO_TIME(SUM(IF(w.endtime, w.endtime, TO_TIMESTAMP(NOW())::TIMESTAMP) - w.starttime)) AS total
  FROM ticket t
  INNER JOIN work_log w
  WHERE t.id = w.ticket
  GROUP BY t.component, t.id, t.summary, t.status
  WITH ROLLUP HAVING IFNULL(id, -1) = -1 OR (t.summary IS NOT NULL AND t.status IS NOT NULL);

ERROR:  syntax error at or near "WHERE"
LINE 11:  WHERE t.id = w.ticket

Edit: I tried changing the WHERE to ON, and that seemed to fix that error; however, I don't know if this is the correct solution. Also, now I get an error with the "WITH ROLLUP HAVING" part. What's the way to do it in PostgreSQL?

Also, I don't know what to replace SEC_TO_TIME with above, or the SUM / IF combo (as IF() is MySQL only--if I swap to CASE, do I need to test separately for 0 and null?). Using TO_CHAR as per http://www.verious.com/qa/sec-to-time-function-in-postgre-sql/ doesn't work because the minutes are always zero with the code given there (i.e. 90 seconds ends up as 00:00:30...)

Edit 2: What I'm trying to end up with, which was the case with MySQL:

Edit 3: Thanks to a_horse_with_no_name and efesar, I've ended up with the following working query:

WITH base AS (
  SELECT t.component AS component, t.status AS status, t.id AS ticket, t.summary AS summary,
      SUM((CASE WHEN w.endtime > 0 THEN w.endtime ELSE CAST(EXTRACT(EPOCH FROM current_timestamp) AS bigint) END) - w.starttime) * INTERVAL '1 second' AS total
    FROM ticket t
    INNER JOIN work_log w
    ON t.id = w.ticket
    GROUP BY t.component, t.id, t.summary, t.status
)
SELECT CONCAT('Component ', component) AS __group__,
    (CASE status WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' END) AS __style__,
    ticket, summary, total
  FROM base
UNION ALL
SELECT CONCAT('Component ', component) AS __group__,
  'font-weight: bold' as __style__, null, 'Total', SUM(total)
  FROM base
  GROUP BY __group__
UNION ALL
SELECT 'All Components' AS __group__, 'font-weight: bold' as __style__, null, 'Total', SUM(total)
  FROM base
ORDER BY __group__, ticket


texts: MySQL is not using index on multiple joins

texts:
I have a simple query which is working as expected: it joins a t2 by the index (type = index in the explain query):

select t1.id
from table1 t1
left join table2 t2 on t1.vendor_id = t2.vendor_id

But when I add one more join, it's not using the index (type = ALL in the explain query):

select t1.id
from table1 t1
left join table2 t2 on t1.vendor_id = t2.vendor_id
left join table3 t3 on t2.vendor_t2_id = t3.vendor_t2_id

How can I fix this, so all tables are joining by an index?


texts: Deleting rows from child table without deleting from parent

texts:
I have 2 tables, connected with inheritance.
I want to delete some rows from the child table. The deletion part works but it deletes rows from the parent as well:
CREATE OR REPLACE FUNCTION earthquakes_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO earthquakes VALUES (NEW.*);
    DELETE FROM ONLY earthquakes WHERE datetime < (now() - '2 days'::interval);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER earthquakes_trigger
        BEFORE INSERT ON earthquakes_ovr
        FOR EACH ROW EXECUTE PROCEDURE earthquakes_trigger();

Parent table : earthquakes_ovr
Child table: earthquakes
I also tried to break up the trigger function, and have the deletion part as trigger function on the child, but that didn't work either.


texts: Add MS SQL Server to virtual machine in Azure

texts:
I'm trying to get TDE in Azure. I read that it's possible only on a virtual machine. I built the virtual box, but didn't add SQL Server when it was created. Can I just install SQL or will I have to start from scratch and recreate the virtual machine.

Thank you for your help.

Ernie_M


texts: PGpool not properly detecting which postgres server is master

texts:
I am in the process of building a resilient database cluster and currently have 3 database servers running postgres 10 and repmgr. repmgr is taking care of the streaming replication including automatic failover if there is a problem with the master. So far this is working fine, if I turn off the master, repmgr detects this and makes one of the standbys into a master.

The next step is for the application servers to read /write to the database. For this I have installed pgpool on the application servers. The application connects to the db on localhost port 9999 (pgpool). Pgpool will detect which database is the master and write to that, it will load balance the reads.

The problem I am having is during failover... initially pgpool is reporting correctly which databases are masters and which are slaves but after a failover it picks up the new master and shows all slaves as down even though one of them is actually still up.

after a failover repmgr looks correct:

usr/pgsql-10/bin/repmgr -f /etc/repmgr/10/repmgr.conf cluster show
 ID | Name       | Role    | Status    | Upstream   | Location | Connection string
----+------------+---------+-----------+------------+----------+-------------------------------------------------------
 1  | icinga-db0 | primary | * running |            | default  | host=icinga-db0 user=repmgr dbname=repmgr connect_timeout=2
 2  | icinga-db1 | primary | - failed  |            | default  | host=icinga-db1 user=repmgr dbname=repmgr connect_timeout=2
 3  | icinga-db2 | standby |   running | icinga-db0 | default  | host=icinga-db2 user=repmgr dbname=repmgr connect_timeout=2

the primary failed but a secondary took over, the other secondary upstream is now the new primary

but pgpool reports this:
psql -U repmgr -p 9999 -c "show pool_nodes"
 node_id |  hostname  | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | icinga-db0 | 5432 | up     | 0.333333  | primary | 99         | true              | 0
 1       | icinga-db1 | 5432 | down   | 0.333333  | standby | 5929       | false             | 0
 2       | icinga-db2 | 5432 | down   | 0.333333  | standby | 12         | false             | 13136

this doesn't match with what repmgr says as db2 should have a status of up.

Does anyone know what the cause of this might be?
Is there a better way to achieve the same outcome?


texts:
I have written a query to fetch a time internal:

select *, (Time + interval '2012-12-18 13:36:47' minute)  from chatnotiservice;

Is it possible to name the (Time + interval '2012-12-18 13:36:47' minute) column?


texts: How to list all JSON properties in an Azure Cosmos DB?

texts:
I will be using SQL queries to get data out of a CosmosDB database. I want to see what nested JSON properties are available to query. Is there a way to do this in the Azure portal, or via a SQL query? For the sample document here, I'd want to list the following JSON properties:
id; parents: familyName, givenName; children: familyName, givenName, gender, grade, pets; address; creationdate; isregistered

I could look at the fields returned in the following query, but maybe there's a better way since the first record may not contain all the possible JSON properties,
SELECT TOP 1 * FROM c


texts: MySQL: how to prevent deadlock on concurrent inserts

texts:
Percona Server 5.6.39-83.1-log

Table structure:

CREATE TABLE `myTable` (
    `column_1` VARCHAR(50) NOT NULL DEFAULT '',
    `column_2` VARCHAR(50) NOT NULL DEFAULT '',
    `column_3` VARCHAR(50) NOT NULL DEFAULT '',
    `column_4` VARCHAR(50) NOT NULL DEFAULT '',
    `column_5` VARCHAR(50) NOT NULL DEFAULT '',
    PRIMARY KEY (`column_1`, `column_2`, `column_3`) USING BTREE,
    INDEX `column_3` (`column_3`) USING BTREE,
    INDEX `column_4_5` (`column_4`, `column_5`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

There are two or more concurrent insert leads to deadlock:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-03-23 18:12:45 7f1c08eb8700
*** (1) TRANSACTION:
TRANSACTION 55665288601, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 10275055, OS thread handle 0x7f1c08c6c700, query id 56392293629 localhost user update
INSERT IGNORE INTO myDB.myTable
(`column_1`, `column_2`, `column_3`, `column_4`, `column_5`)
VALUES
('a1', 'a2', 'a3', 'a4', 'a5'),
('b1', 'b2', 'b3', 'b4', 'b5'),
('c1', 'c2', 'c3', 'c4', 'c5'),
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 337207190 page no 7370 n bits 200 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288601 lock mode S locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 55665288598, ACTIVE 0 sec inserting, thread declared inside InnoDB 4965
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 35
MySQL thread id 10275057, OS thread handle 0x7f1c08eb8700, query id 56392293627 localhost user update
INSERT IGNORE INTO myDB.myTable
(`column_1`, `column_2`, `column_3`, `column_4`, `column_5`)
VALUES
('a1', 'a2', 'a3', 'a4', 'a5'),
('b1', 'b2', 'b3', 'b4', 'b5'),
('c1', 'c2', 'c3', 'c4', 'c5')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 337207190 page no 7370 n bits 200 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288598 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 337207190 page no 83133 n bits 144 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288598 lock_mode X insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

Why is this happening?
Despite the fact that I do not use auto-increment in the primary key and use IGNORE in query.
How should I avoid this problem?


texts: First date in a week, not exceed from the month, start from specific day

texts:
I want to get first date of week from date, but not exceed from first date of month. This is the query i've tried :

SET @date:='2014-10-03';
SELECT
IF(DATE_ADD(@date, INTERVAL(1-DAYOFWEEK(@date)) DAY)<DATE_FORMAT(@date ,'%Y-%m-01'),
DATE_FORMAT(@date ,'%Y-%m-01'),
DATE_ADD(@date, INTERVAL(1-DAYOFWEEK(@date)) DAY)) AS result

The result : 2014-10-01, because in DATE_ADD(@date, INTERVAL(1-DAYOFWEEK(@date)) DAY) , function DAYOFWEEK() start day of week  is from sunday by default.

What can i add into my query so i can set the start day of week ?

for eg. (Desired) result date :

if i set the start day of week is Thursday then result should be 2014-10-02
if i set the start day of week is Tuesday then result should be 2014-10-01


texts: Does an order by clause have a negative effect on performance?

texts:
I couldn't find the answer by googling.

I only over heard a senior dev telling a junior dev that using an order by clause has a negative effect on performance and he shouldn't use it(Oracle).

In what circumstances can an order by clause effect negatively on performance?

Update

I found Bad performance of SQL query due to ORDER BY clause. Is there any other circumstances where an order by clause may negatively effect performance?


texts: How to REVOKE ALL ON db.table FROM user after GRANT ALL db.*

texts:
I cannot revoke rights form a user which I gave him before

I gave a GRANT ALL ON [database].* TO 'user'@'%', because I need some user has access to all tables unless one of them. Then I wrote:
REVOKE ALL ON [database].table1 FROM 'user'@'%', but it says that: ERROR 1147 (42000): There is no such grant defined for user 'user' on host '%' on table 'table1';


texts:
My new job has some sql server 2014.  I am an Oracle DBA.

I need to help a client.

They are having issues creating the ODBC connection from their laptop to the database server (or whatever MS calls a database)

I have access to MS SQL Server 2014 Management Studio.

Questions:
How can I create an ODBC connection to this database on my laptop to test and provide a procedure for the client?

Do I need to create myself a user to test?

(In oracle I would install the client and give them a tnsnames and an alias to use and maybe use tnsping or sqlplus to check the connection)

EDIT1:

I use Oracle SQL Develper and it uses jdbc.  maybe you can help me setup my first JDBC connection.

EDIT2:
Tried the ODBC data source and it failed

EDIT3:
Checked if 1433 is enabled and looks like something is connecting to this server;

Active Connections

Proto  Local Address          Foreign Address        State

TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING

TCP    xxx.xx.xx.xxx:3389   xxx.xx.xx.xxx:56010    ESTABLISHED

TCP    xxx.xx.xx.xxx:56984  xxx.xx.xx.xxx:2074     ESTABLISHED


texts: MariaDB audit plugin logs all queries instead of DML only

texts:
I was testing the "server_audit" plugin of MariaDB, which I configured like this:

plugin-load-add = server_audit
server_audit_logging = ON
server_audit_events = QUERY_DML
server_audit_output_type = FILE
server_audit_file_path = /path/to/audit.log
server_audit_query_log_limit = 1048576
server_audit_file_rotate_size = 1073741824
server_audit_file_rotations = 1

and I am currently running MariaDB 10.1.29 with MariaDB Audit Plugin version 1.4.3.

Based on the docs, the value QUERY_DML should be

  Same as QUERY, but filters only DML-type queries (DO, CALL, LOAD DATA/XML, DELETE, INSERT, UPDATE, HANDLER and REPLACE statements)

emphasis mine.

After restarting the server, the log files has correctly been created, however it is logging also other queries, like for example plain SELECTs.

Is my configuration somewhat incorrect, or is it perhaps an error on MariaDB side?


texts: mysql on duplicate key update the entire row

texts:
this is a jsp code

 PreparedStatement pstmt =con.prepareStatement("select ssn,name,gender,bod,address,profession,phone from contacts ");
        ResultSet rss = pstmt.executeQuery();

        while(rss.next()){
            stmt = conn.createStatement();
String sql1 = "INSERT INTO contacts (id,ssn,name,gender,dob,address,profession,phone) VALUES (default,'" +rss.getString(1) + "','"+rss.getString(2) + "','"+rss.getString(3)+"','" +rss.getString(4) + "','"+rss.getString(5) +"','"+rss.getString(6)+"','"+rss.getString(7)+"' on duplicate key update ssn=VALUES("+rss.getString(1)+")";
            stmt.executeUpdate(sql1);
            out.println("
");
            out.println("");        out.println(rss.getString(1));out.println("	");
            out.println("");        out.println(rss.getString(2));out.println("	");
            out.println("");        out.println(rss.getString(3));out.println("	");
            out.println("");        out.println(rss.getString(4));out.println("	");
            out.println("");        out.println(rss.getString(5));out.println("	");
            out.println("");        out.println(rss.getString(6));out.println("	");
            out.println("");        out.println(rss.getString(7));out.println("	");
out.println("
");
                            }

out put:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on duplicate key update ssn=VALUES(1)' at line 1

note: Server version: 5.7.10-log MySQL Community Server


texts: OLE DB provider "MSOLEDBSQL" with SQL Server not supported?

texts:
I have been using linked server with the old provider (SQLNCLI) without any issue, as recommended by Microsoft, I'm planning to switch to new provider (MSOLEDBSQL). I'm able to add linked server using following T-SQL after installing the drivers
EXEC sp_addlinkedserver
   @server=N'SQL02\DEV1',
   @srvproduct=N'',
   @provider=N'MSOLEDBSQL',
   @datasrc=N'SQL02,1933';

Unfortunately, getting following error when I try to query new linked server:
Queries that I tried:
--- example 1
select * from OPENQUERY ([SQL02\DEV1], 'select name from sys.databases');

--- example 2
select name from [SQL02\DEV1].master.sys.databases;

--- example 3 (without linked server dependency)
SELECT c.* FROM OPENROWSET(
      'MSOLEDBSQL'
    , 'Server=SQL02,1933;Database=master;Integrated Security=True;'
    , 'SELECT name FROM sys.databases;'
    ) c;

Getting same error from all examples:

Out-of-process use of OLE DB provider "MSOLEDBSQL" with SQL Server is not supported.

Does that really mean SQL-2016 is not supported for using new provider MSOLEDBSQL especially in Linked Servers, or is there anything I missed other than re-installing drivers and restarting the SQL Server.


texts: If view A is built atop other views and tables, does a user need permission to only view A, or everything down?

texts:
I'm talking about SQL Server in particular. I suppose a user tried it and got an error (you don't have select permissions on hidden underlying view B not explicitly mentioned in query). The main view A joins a table and another view (view B) together, and they don't have permission for view B.

I'm not in charge of permissions for the database, but I'm curious how it works.

Do you need to grant permissions to every object built underneath the views? Or just the view itself? Or either one?

I thought one of the purposes of views was a snapshot of underlying objects that you DON'T want to give full permissions for. That's what is unusual to me.


texts: What's better - setting up Always On fresh and migrating data, or using existing server with data and adding Always On?

texts:
I have an existing SQL Server instance with three databases and a small amount of data.

I've been asked to create an Always On Availability Group now. The existing server would need to have the drives re-sized and only has 3 vCPUs.

So if you had a choice, what would be easiest/safest? Spin up the new servers, setup Always On, then migrate the data from the existing server?

Or should I keep the existing server and instance and add Always On Availability Groups to the existing SQL Server instance?


texts:
Hi all I would like to apply one patch(pg_dump_grouplock.patch) in my PostgreSQL 9.2 database
regarding lock table issue advised on below link

http://www.postgresql-archive.org/pg-dump-and-thousands-of-schemas-td5709766i40.html

I have never applied the patch, can anybody let me know the step how to apply the patch.


texts:
I have a problem with the design of a database: In my Android application i need to store the upload (the status, so if the file is in upload it will report the % of the upload) of a file and a list of all files uploaded.

First, i was design the database to something like this:

Files
----------------
_id INT PK,
status INT,
progress INT,
imageupload BLOB

So status will be the status of the upload which will be

0. Unknown
1. In upload
2. Error
3. Success

While imageupload is just a class serializable which contains: imageTitle, imagesUri (A List), album, privateImage.

(i said that in case exists another better way to design it which i didn't know and avoid using a blob)

With this design i could see if a file is in upload by reading the status WHERE status != 3 AND status != 2

Another design which could be used is to use two tables where in one table are stored the in upload files and in another the already uploaded.

With the two tables i avoid using status but it will let me execute two queries inside to select all files and order them by IN-UPLOAD/UPLOADED.

What is the best practice? Is one table prefered over two tables? I would use one table since it's more easy and can be edited without problems. With the second version i should move the record from in_upload table to uploaded table (which could be problematic).

If it's important i'm using SQLite.