texts: Creating an array from multiple columns without NULL elements

texts:
I'm trying to build a query to aggregate together multiple columns in a legacy table stored in a similar structure as below:

CREATE TEMPORARY TABLE foo AS
SELECT * FROM ( VALUES
  (1,'Router','Networking','Sale',NULL),
  (2,NULL,'Router','Networking','Sale'),
  (3,NULL,NULL,'Networking','Sale'),
  (4,NULL,NULL,NULL,NULL)
) AS t(id,tag_1,tag_2,tag_3,tag_4);

An example of NOT WHAT I WANT

This is an example of the query I want to build:

SELECT ID, json_build_array(Tag_1, Tag_2, Tag_3, Tag_4) AS tags
FROM table

The problem is that the above query adds the NULL values from the rows to the array:

ID  Tags
--------------------------------------------------
1   ['Router', 'Networking', 'Sale', null]
2   [null, 'Router', 'Networking', 'Sale']
3   [null, null, 'Networking', 'Sale']
4   [null, null, null, null]

I want to avoid having to write an overly complicated CASE WHEN statement to filter out the NULLs and I'm still new to working PostgreSQL's JSON datatypes. Is there anyway I can avoid including NULLs when building a JSON array in Postgres?


texts: System disk run out of space when running heavy SQL queries on SQL Server 2012

texts:
I'm quite new to SQL Server 2012, I would be grateful if someone can help.
I have restored a copy of a huge database to SQL Server 2012 and I tried to run some simple queries against it.

I'm trying to run a SELECT query against a database table of 136898115 lines. This SELECT query only has a simple WHERE clause. Every time I run this query, it fails because the system disk (the partition where Windows is installed - C:\) run out of space (this partition has only 6GB free space), and I don't understand why. I defined my tempdb to be on a different drive, which has more than 14 terabytes of free space. Of course that my database is located on a different drive too.

What makes my system partition run out of space? Is it the page file?


texts: Moving mysql database to new partition with different partition "type"

texts:
Sorry, Possibly a dumb question, but something which needs clarification.

Currently mysql is stored on sda which is around 1TB. Now our DB has outgrown that, almost !!. So trying to create new partition of 3 TB so that we can move DB to new partition.

Current sda is partition type 83 (aka Linux)

We added new sdb partition of 3 TB but creating partition type with "linux" can only go max up to 2 TB.

My question is, does it matter if we move SQL files to "new" partition with different partition "type" ? e.g. 85 (linux Extended, which can go up to max size of 16TB)


texts: Designing a database structure for huge sets of form data

texts:
I am converting a form that takes up 35 pages of A4 size to a web based version, and will need to retain the data for each form submission in a MySQL database.

The form is complex and we could be looking at over 500 individual fields for each form submission.

I've been reading about the EAV data approach, which seems like it might be a good option. The info I was reading for EAV however suggested it was only a good choice for sparse matrix data, which I imagine this will not be.

500 columns seems like bad design, what do you think?


texts: What ways are there to limit the processor / ram requirements of a specific query?

texts:
Every once in a while, I need to run some very expensive query on a production server. Think spaghetti joins. This is likely to affect the server operation, and I don't want that.

What ways are there to limit the processor / ram requirements of a specific query, basically trading speed and freshness for reliability?


texts: How can I query the SQL Server Compatibility Level into a variable?

texts:
I know to query the current Compatibility Level of a database with

declare @dbname sysname
select @dbname = db_name(0)
exec sp_dbcmptlevel @dbname

But this seems to return its result via print and I have found no way to assign the value to a variable and use it to control if ... else ... constructs to execute different code depending on current setting.


texts: Determine tables / query from Missing Join Predicate event

texts: On SQL Server 2008, in the profiler, I have a Missing Join Predicate event class that happened at 3:30am StartTime.  How do I get more information?  What query was being executed, or tables were being joined?


texts: How do i subtract a number of days from a prepared statement parameter in db2?

texts:
I have a C# application using OLEDB to connect to a DB2 database.  Basically, I want to do this:

SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
    SUM(CALC.SUBTOTAL) AS REVENUE
FROM (
        SELECT
            CASE
                WHEN O.DATE < ? - 21 DAYS THEN 1
                WHEN O.DATE >= ? - 21 DAYS AND O.DATE < ? - 14 DAYS THEN 2
                WHEN O.DATE >= ? - 14 DAYS AND O.DATE < ? - 7 DAYS THEN 3
                WHEN O.DATE >= ? - 7 DAYS THEN 4
            END AS WEEK,
            O.ID, O.SUBTOTAL
        FROM COMPANY.ORDERS AS O INNER JOIN
            COMPANY.LOCATIONS AS L ON O.LOCATION_ID = L.ID INNER JOIN
            COMPANY.STORES AS S ON O.STORE_ID = S.ID
        WHERE O.DATE >= ? - 28 DAYS
            AND O.DATE <= ?
            AND S.NUMBER = ?
   ) AS CALC
GROUP BY CALC.WEEK;

However, when I run that, I get this:

An expression with a datetime value or a labeled duration is not valid.. SQLCODE=-182, SQLSTATE=42816, DRIVER=3.63.108

And if i change it to wrap the ? parameter with a TIMESTAMP function thusly:

SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
    SUM(CALC.SUBTOTAL) AS REVENUE
FROM (
        SELECT
            CASE
                WHEN O.DATE < TIMESTAMP(?) - 21 DAYS THEN 1
                WHEN O.DATE >= TIMESTAMP(?) - 21 DAYS AND O.DATE < TIMESTAMP(?) - 14 DAYS THEN 2
                WHEN O.DATE >= TIMESTAMP(?) - 14 DAYS AND O.DATE < TIMESTAMP(?) - 7 DAYS THEN 3
                WHEN O.DATE >= TIMESTAMP(?) - 7 DAYS THEN 4
            END AS WEEK,
            O.ID, O.SUBTOTAL
        FROM COMPANY.ORDERS AS O INNER JOIN
            COMPANY.LOCATIONS AS L ON O.LOCATION_ID = L.ID INNER JOIN
            COMPANY.STORES AS S ON O.STORE_ID = S.ID
        WHERE O.DATE >= TIMESTAMP(?) - 28 DAYS
            AND O.DATE <= TIMESTAMP(?)
            AND S.NUMBER = ?
    ) AS CALC
GROUP BY CALC.WEEK

I get this error:

The invocation of routine "TIMESTAMP" is ambiguous. The argument in position "1" does not have a best fit.. SQLCODE=-245, SQLSTATE=428F5, DRIVER=3.63.108

As a bonus question, short of creating a PL/SQL function, is there a way to use the same parameter for all the timestamp locations instead of supplying the same value 8 times?


texts: PostgreSQL-Backup from windows hard drive to linux

texts: I'm in a bit of a pickle here. I managed to copy the whole postgresql installation directory to a backup drive but how would I retrieve it from Linux with the same architecture and version. Is this possible? I wasn't able to do a backup.


texts:
I'm after running a query with a subquery (perhaps inefficient) that using a regular expression pattern it can return how many instances of a characters are included with a given record.

In doing so, I came across something like:

SELECT t."page",
            product_tree_length,
           Count(*),
  FROM     "Table" t
  ,
  LATERAL (SELECT (LENGTH((regexp_matches(t."page", '^.+\/c\/(.+?)(?=\/\?|\?|\/$).*$', 'g'))[1]) - LENGTH(REPLACE(((regexp_matches(t."page", '^.+\/c\/(.+?)(?=\/\?|\?|\/$).*$', 'g'))[1]),'/','')))) AS "product_tree_length"
  where t.page like '%\/c/%'
  GROUP BY 1, product_tree_length;

However, in selecting the product_tree_length in the main query, the returned data type is a record and not an integer as expected.
If I try to cast the value, PostgreSQL raise an exception that the operation cannot be completed.

Any idea on what I am doing wrong?

Thanks


texts:
I have two tables: orders and ordersdetail...(partial table)

orders- id(primary key), amount, date( index)

orderdeails- id(primary key), skunitid, quantity, order_id

Each order has multiple order details.

Query:

SELECT Sum(orders.amount), SUM(ordersdetails.quantity)
FROM orders joins orderdetails on ordersdetail.order_id = orders.id
WHERE {{conditions on date column of order table}}

I know its the wrong query, but I want data from both tables in single query (single iteration). When I use a join, the order table's amount comes so many times. How I can get the sum of amount and sum of quantity in single query (I have an index on date column in the order table which has million rows).

I need an efficient way..


texts:
My simple SELECT statement on a view in Postgres 9.3.1 is pretty slow. The receipts table has 20 million entries and the other one has around 17k.

But I still think it should be faster than ~16 seconds?

Table receipts:

     Column      |            Type           | Modifiers | Storage     | Stats target  | Description
-----------------+---------------------------+-----------+-------------+---------------+--------------
 profitcenter    | character varying(5)      | not null  | extended    |               |
 receiptnumber   | bigint                    | not null  | plain       |               |
 receiptposition | character varying(6)      | not null  | extended    |               |
 date            | date                      | not null  | plain       |               |
 customerid      | integer                   |           | plain       |               |
 vkorg           | character varying(4)      |           | extended    |               |
 artikelnummer   | integer                   |           | plain       |               |
 price           | numeric(18,2)             |           | main        |               |
Indexes receiptnumber, date and customerid as B-Tree.

Table cc_add_entry2:

    Column     |          Type          | Modifiers | Storage     | Stats target  | Description
---------------+------------------------+-----------+-------------+---------------+--------------
 receiptnumber | bigint                 | not null  | plain       |               |
 customerid    | bigint                 |           | plain       |               |
 user          | character varying(255) |           | extended    |               |
 note          | text                   |           | extended    |               |
 date          | date                   |           | plain       |               |
Indexes receiptnumber, customerid as B-Tree.

The view is simple:

 SELECT s.receiptnumber,
    s.receiptposition,
    s.date,
    COALESCE(b.customerid, (s.customerid)::bigint) AS customerid,
    s.price
   FROM (dashboard.receipts s
     LEFT JOIN dashboard.cc_add_entry2 b ON ((s.receiptnumber = b.receiptnumber)));

EXPLAIN ANALYZE over a simple query:

explain analyze select * from test2 where customerid = 520001215;

 Hash Left Join  (cost=582.40..1086787.72 rows=100185 width=37) (actual time=2124.048..15937.744 rows=52 loops=1)
   Hash Cond: (s.receiptnumber = b.receiptnumber)
   Filter: (COALESCE(b.customerid, (s.customerid)::bigint) = 520001215)
   Rows Removed by Filter: 20036943
   ->  Seq Scan on receipts s  (cost=0.00..599969.96 rows=20036996 width=29) (actual time=0.904..7187.563 rows=20036995 loops=1)
   ->  Hash  (cost=360.51..360.51 rows=17751 width=16) (actual time=5.850..5.850 rows=17751 loops=1)
         Buckets: 2048  Batches: 1  Memory Usage: 694kB
         ->  Seq Scan on cc_add_entry2 b  (cost=0.00..360.51 rows=17751 width=16) (actual time=0.005..2.537 rows=17751 l
oops=1)
 Total runtime: 15937.962 ms


texts: CREATE NONCLUSTERED INDEX ONLINE, DROP EXISTING

texts:
I want to add three included columns to an existing non-clustered index. With that I'm solving performance issues in the database.

Here is some information about the index:
Rows: 112 Millions
Disk space: 30 GB

I'm using SQL Server 2008 R2 Enterprise Edition.

I'm running CREATE INDEX ... WITH DROP EXISTING = ON, ONLINE = ON

In my QA environment I'm currently running the CREATE INDEX statement. It has been running 1 1/2 hours from now and I run on purpose an INSERT statement in another SSMS window, this statement is blocked since the CREATE INDEX operation is running.

In production this database is being accessed 24x7x365, so I can not accept so much time of blocking, but there's a time when the database has low access (3AM - 6AM).

I was expecting that the WITH ONLINE = ON behaves different. There's a way to prevent so much blocking? Or should I ask for a long maintenance window for the CREATE INDEX operation?

Thanks in advance for any response.


texts: Is it recommend to create a table for addresses?

texts:
I have a table persons with the columns per_id, per_passport, per_name and per_address.

Two or more persons can live in the same address (like families or room-mates) so if this happened, the data is repeated. I think it should be avoided due to a normal form.

So should I create a table addresses to store all the addresses of the persons?

Also some persons may be in the home address or in the work address. It means persons can have multiple addresses. So should I create a per_add intermediate table?

I have the same problem to store email addresses and phone numbers.

I use PostgreSQL.


texts: Where to download SQL Server 2019 Express LocalDB Installer MSI from

texts: SQL Server 2019 is released but I there doesn't appear to be a LocalDB version available for download. Does anyone know if there will be a LocalDB release or if 2017 is the last version?


texts:
How does mysql triggers and rollback working together? I need to know how this is working together? I can rollback if i proceed with more than one sql statement because it is in same query but it seems different with triggers because it proceeds seperately

If my sql statement fails and raises an error then trigger still proceeds? If it does how can i stop trigger?

And if my sql statement was successfull and trigger raised and error, how will i know and rollback?

Thanks


texts:
I have a fairly specific set of data I am trying to display.

I have a dataset of helpdesk ticket data.  I would like to display a line chart that shows tickets opened and tickets closed by day, over the last 30 days.  I've been able to display one of these just fine, by adding an expression to my Category group:

=Day(Fields!Created.Value)

The 'Created' line displays just fine, yet the 'Closed' line seems to jump around at random.  It seems that I can't further group by day on the 'Closed' column; this breaks the chart even further.  Here's an example image:

Please let me know if I haven't been clear in any way.

EDIT:
I would like to be clear that I've explored the option of creating a new dataset for this chart.  I've been able to group counts by day, but only for 'Created' or 'Closed', not both. For example:

If there's a way to combine these into one SQL query, that would fix my problem.

*****SOLUTION UPDATE*****

Here's is the query that has given me the data I need.  Thanks, mmarie!

WITH X AS (SELECT      TOP (30) n = ROW_NUMBER() OVER (ORDER BY m1.number) - 1
FROM          [master].dbo.spt_values AS m1 CROSS JOIN
                        [master].dbo.spt_values AS m2), last30days AS
    (SELECT      dt = cast(DATEADD(DAY, (- 1 * n), getdate()) AS date)
      FROM           X)
    SELECT      dt.dt, CASE WHEN tcr.CreatedCount IS NULL THEN 0 ELSE tcr.CreatedCount END AS 'CreatedCount', CASE WHEN tcl.ClosedCount IS NULL
                             THEN 0 ELSE tcl.ClosedCount END AS 'ClosedCount'
     FROM           last30days dt LEFT JOIN
                                 (SELECT      COUNT(a.TicketNbr) AS CreatedCount, CAST(a.date_entered AS date) AS 'DayCreated'
                                   FROM          v_rpt_Service a
                                   WHERE       (closed_by LIKE @tech + '%')
                                   GROUP BY CAST(date_entered AS date)) tcr ON dt.dt = tcr.DayCreated LEFT JOIN
                                 (SELECT      COUNT(a.TicketNbr) AS ClosedCount, CAST(a.date_closed AS date) AS 'DayClosed'
                                   FROM           v_rpt_Service a
                                   WHERE       (closed_by LIKE @tech + '%')
                                   GROUP BY CAST(date_closed AS date)) tcl ON dt.dt = tcl.DayClosed


texts:
I have multiple views with union like this:

select column1, column2 from mytable
union
select cast(extcol as nvarchar2(15)), cast(extcol2 as nvarchar2(10)) from exttable

But later I change the size of column2 to nvarchar(20) and now I need to change cast(extcol2 as nvarchar2(10)) to cast(extcol2 as nvarchar2(20)), in every view or reference that could be exist.

I tried it:

select column1, column2 from mytable
join
select cast(extcol as mytable.column1%TYPE), cast(extcol2 as mytable.column2%TYPE) from exttable

It throw me an error:

SQL Error: ORA-00911: invalid character
00911. 00000 -  "invalid character"
*Cause:    identifiers may not start with any ASCII character other than
           letters and numbers.  $#_ are also allowed after the first
           character.  Identifiers enclosed by doublequotes may contain
           any character other than a doublequote.  Alternative quotes
           (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters.  For all other contexts, consult the SQL Language
           Reference Manual.

What can I do?


texts: When do I need more than one Oracle Instance on the same server?

texts:
I have one application server separate from an Oracle server (one physical server).  I have three instances on one Oracle Database Server (another physical server).

I want to have three environments to work in, Development, Test, and Production.

Should these be three Instances or three Schemas that use the same Instance?  Edit:  The server can physically handle it either way (i.e. has enough space, memory, and so on.)


texts: Customer just bought an Oracle license - am I allowed to install Oracle 11.2.0.4 without support contract?

texts:
I understand that when I buy an Oracle license, I can choose which version I install, these days either 11gR2 or 12c. But which patch level am I eligible to install? Is it 11.2.0.1 which was released in 2009 and can be downloaded from their website or can I download 11.2.0.4 from My Oracle Support which would be the latest full installation?

Given I'm only eligible for 11.2.0.1 - what happens when I stumble over one of it's many bugs? How would I proceed to have it fixed by Oracle?


texts: How to find queries that timed out in MongoDB?

texts:
Is it possible to find queries in the system.profile-Collection of a MongoDB-database that did not finish due to a timeout?

I found some queries that ran longer than the time specified in $maxTimeMS.

db.coll.find(
  { millis: {$gt: 1300}, "command.maxTimeMS": NumberLong(1300) },
  {millis: 1}
).sort(
  {millis: -1}
)

But they only ran a couple of milliseconds longer, and did not have a field error or timeout. Are there fields that indicate a timeout?


texts: Why is "INSERT INTO table;" a syntax error?

texts:
I have this:
    CREATE TABLE people
    (
        id              bigserial,
        timestamp       timestamptz DEFAULT now() NOT NULL,
        PRIMARY KEY     (id)
    );

    CREATE TABLE "personal information"
    (
        id                  bigserial,
        "person id"         bigint NOT NULL,
        timestamp           timestamptz DEFAULT now() NOT NULL,
        "data's timestamp"  timestamptz,
        "field"             text NOT NULL,
        "value"             text,
        PRIMARY KEY         (id),
        FOREIGN KEY         ("person id") REFERENCES people (id) ON UPDATE CASCADE ON DELETE CASCADE
    );

    INSERT INTO people RETURNING id; -- Syntax error.
    INSERT INTO people; -- Syntax error.

Why do my INSERTS fail? It's a fact that all the columns in the "people" table are automatic: the id column is a bigserial, and the timestamp one is always set to the current time when it was inserted. Thus, there is no need for me to specify any columns to be inserted, as all of them are automatic.
So why is it a syntax error? What does it want me to do? This makes me feel as if I'm not doing this right somehow, but having a bunch of "personal data" fields in the "people" table would be redundant since that data will be stored in the "personal information" table. For example, the "field" might be "social security number" or "first name", so I just grab whatever record is the latest (based on timestamp) for that "person id" to get their social security number or first name.
If I were to store those values as columns in "people", it would be pointless redundancy, and it would make it a major PITA whenever I introduce a new kind of "personal information field" in the future, as I'd have to add columns to the "people" table. So I really hope that my efficient and smart (IMO) structure is not somehow incompatible with PostgreSQL, and that I'm just missing some minor syntax detail.


texts: Calculating the number of times a motive was cited over a given period

texts:
I'm using PostgreSQL 9.4. I have the following surveys table. Each row includes a motive that was cited by the respondent, represented by the motive_value column (think of it as motive_id).

---------------------------------------
id | var      | motive_value | period |
---------------------------------------
1  | 9        | 1            | 1      |
2  | 7        | 2            | 2      |
3  | 2        | 5            | 1      |
4  | 3        | 3            | 1      |
5  | 6        | 1            | 2      |
---------------------------------------

And motives table. There are around 20 motives, but for the sake of brevity I'm including five.

-----------------------------
id | value     | name       |
-----------------------------
1  | 1        | Blocked     |
2  | 2        | Delivery    |
3  | 3        | Forgotten   |
4  | 4        | Info        |
5  | 5        | Other       |
-----------------------------

So, I need to get, grouped by period descending, the top 5 most cited motives. ( note that, most cited motives for the entire surveys table, and not for a given period).

Here's a table that represents what I'm after.

------------------------------------------------------------------
| period | total | blocked | delivery | forgotten | info | other |
------------------------------------------------------------------
| 2      | 2     | 1       | 1        | 0         | 0    | 0     |
| 1      | 3     | 1       | 0        | 1         | 0    | 1     |
------------------------------------------------------------------

The total is the number respondents, or survey rows, in a given period. It's not necessary, but it's nice to have. I'd prefer not to have it if it adds lots of complexity. The other columns represent the motives, and the number of times each motive was cited. For example, in period 1 there were 3 surveys, and one of them cited the blocked motive.

I started with this query. It gets me the top 5 most cited motives.

SELECT
    motive_value,
    motives."name" AS motive,
    COUNT(motive_value) AS sample_size
FROM surveys
INNER JOIN motives ON surveys.motive_value = motives."value"
WHERE var BETWEEN 0 AND 10
GROUP BY motive_value, motive
ORDER BY sample_size DESC
LIMIT 5

I also wrote this query for one motive. It brings me, grouped by period, the number of times a motive was cited.

SELECT
    period,
    COUNT(motive_value) AS sample_size,
    motives."name" AS motive
FROM surveys
INNER JOIN motives ON surveys.motive_value = motives."value"
WHERE (var BETWEEN 0 AND 10) AND motives_value = 1
GROUP BY period, motive
ORDER BY period DESC

The trouble is I need one query for each one of the top 5 motives in the first query. I also need the motives as columns, as shown in my example table. So I'm not sure how to combine those queries, or even create the table where motives are columns.


texts: Mysql preventing truncate and drop table statements for a specific table

texts:
I have a MySQL table that serves as an immutable event store. I added triggers to prevent rows from being updated or deleted;

CREATE TABLE t2 (
  str VARCHAR(32),
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6)
);

CREATE TRIGGER stop_update BEFORE UPDATE ON t2
FOR EACH ROW
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'UPDATE not allowed!';

CREATE TRIGGER stop_delete BEFORE DELETE ON t2
FOR EACH ROW
  SIGNAL SQLSTATE '45007' SET MESSAGE_TEXT = 'DELETE not allowed!';

That part works fine.
But I am unable to prevent the TRUNCATE TABLE or DROP TABLE statements on that specific table.

MySQL doesn't support DDL triggers. Currently I have revoked the drop and truncate permissions for the web app user.

But I would like to add some sort of warning/notice/tripple confirmation dialog thing for the root user as well, just in case someone down the lane accidentally/mistakenly/intentionally tries to clear/drop the table.

Is it possible to add some kind of stored procedure [or anything] in MySql that can prevent a drop/truncate on a specific table? Or at least can warn that this is a special table and should not be removed or cleared.


texts: On Prem SQL Server: Log shipping via Azure Blob Storage: Possible?

texts:
This is about on premise, regular SQL Server 2014, Standard edition.

This is NOT about SQL Azure. The only part of Azure in this conversation is Azure Blog Storage, as a transport for some bits (SQL backups and logs)

Scenario:

Primary SQL Server is in data center 1.

Backup is in data center 2.

In SQL 2014, is it possible to use the "backup to url/azure blob storage" to be the log shipping pipeline?

We like the idea of backup of db and logs to azure blob storage. Great, easy, automatic off site storage of backups.

Would be awesome to use the same pipeline for log shipping to backup db server.

Doable?

(Notes: Neither server is member of a domain. These are two stand alone servers, with no AD or other relationship with each other.)


texts: MySQL : Stored Procedure from Trigger giving duplicate results

texts:
I have created a stored procedure to insert data into an audit table. The procedure accepts an argument that takes table name and same value is inserted into the audit table along with PK.

When i'm calling this stored procedure from AFTER DELETE trigger, from multiple tables it is inserting duplicate values. Basically, same PK value is getting inserted for all table names in the audit table. So, if i delete a record from a table, number of inserts in audit table for single delete = number of triggers.

CREATE PROCEDURE sp_ins_log(
trigger_name VARCHAR(64),
column_name VARCHAR(64),
column_value VARCHAR(255),
requested_by VARCHAR(100))
INSERT INTO log (request_id,reference_schema, reference_table, reference_field, reference_id, action_performed, requested_by)
SELECT
(SELECT trx_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_mysql_thread_id = CONNECTION_ID()),
EVENT_OBJECT_SCHEMA,
EVENT_OBJECT_TABLE,
column_name,
column_value,
CONCAT(ACTION_TIMING,'_',EVENT_MANIPULATION),
IFNULL(requested_by,CURRENT_USER())
FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME = trigger_name AND TRIGGER_SCHEMA = SCHEMA();

CREATE TRIGGER tr_table_ad
AFTER DELETE
ON table_name FOR EACH ROW
CALL sp_ins_log('table_name',old.id);

But, if i put the same insert query individually in each trigger, it works fine.

Reproduced the issue DB Fiddle

Compare this with direct execution of INSERT query inside SP.


texts: SQL Server database design for "archived but available" data

texts:
We have this large database (>1TB) that we intend to "shrink".  The database revolves around one main entity, let's call it "Visit".  For discussion, let's say it is a database for a medical practice.

There are a total of 30 visit "types", such as procedure, annual, follow-up, immunisation etc, each of which is a subsidary table to "Visit", e.g. "visit_immuno".

The database has accummulated some 12 years of data since 2000.  Someone has proposed that we keep about 3 years of data in the "live" version and have the rest live in an "old_data" database.  The date is ONLY stored in the "Visit" table since it is normalised.  The Visit table also contains a ROWVERSION column and a BIGINT pseudo-identity (clustered) column.  For all intents and purposes, let's say the clustering key is populated by a SEQUENCE (SQL Server 2012 Enterprise) - we shall name it cid.

The visit.date is not always in the same order as the clustering key, for example when a doctor goes on extended visitations and returns with his "briefcase" of data, it gets merged into the main table.  There are also some updates to the "visit" table that will cause the ROWVERSION column to be out of sync with both the cid and date columns - to put it simply, neither ROWVERSION nor cid would make suitable partition keys for this reason.

The business rule for removing data from the "live" is that the visit.date must be greater than 36 months and a child visit_payment record must exist.  Also, the "old_data" database does not contain any of the base tables except visit%.

So we end up with:

Live DB (daily use) - All tables
Old-Data DB - older data for the visit% tables

The proposal calls for a Combined DB that is a shell containing Synonyms to ALL the base tables in the Live DB (except visit%) plus Views that UNION ALL across the visit% tables in the two databases.

Assuming the same indexes are created in the Old-Data DB, will the queries perform well on the UNION-ALL Views?  What type of query patterns might trip up the execution plan for the UNION-ALL Views?


texts: How long time does it take to TRUNCATE 50 million rows?

texts:
I want to TRUNCATE (or remove all rows in) a table with 50 million rows.

The database is in use in production.

How long time will the TRUNCATE operation take?


texts:
I have a query:

SELECT
  user_details.id , user_details.first_name , user_details.last_name,
  user_accounts.name account_name, jtl0.account_id account_id,
  user_details.title , user_details.job_function ,
  user_details.contact_data_status , user_details.assigned_user_id
FROM user_details
LEFT JOIN  user_accounts_user_details jtl0
  ON user_details.id=jtl0.contact_id AND jtl0.deleted=0
LEFT JOIN  user_accounts user_accounts
  ON user_accounts.id=jtl0.account_id AND user_accounts.deleted=0
where user_details.deleted=0
ORDER BY account_name ASC LIMIT 0,21;

Without the ORDER BY / LIMIT, the query returns immediately, but when I add ORDER BY account_name ASC to this takes 5 minutes. I have tried several combinations of indexes on user_accounts but have no success.

Here is the EXPLAIN result of the query:

+----+-------------+----------+--------+--------------------------------------------------------------+-----------------------+---------+-------------------------------+---------+----------------------------------------------+
| id | select_type | table    | type   | possible_keys                                                | key                   | key_len | ref                           | rows    | Extra                                        |
+----+-------------+----------+--------+--------------------------------------------------------------+-----------------------+---------+-------------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | user_details | ref    | idx_contacts_del_last,idx_cont_del_reports,idx_del_id_user   | idx_contacts_del_last | 2       | const                         | 1554822 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | jtl0     | ref    | idx_contid_del_accid                                         | idx_contid_del_accid  | 113     | salesify_db.contacts.id,const |       1 | Using index                                  |
|  1 | SIMPLE      | user_accounts | eq_ref | PRIMARY,idx_accnt_id_del,idx_accnt_assigned_del,idx_oss_name | PRIMARY               | 108     | salesify_db.jtl0.account_id   |       1 |                                              |
+----+-------------+----------+--------+--------------------------------------------------------------+-----------------------+---------+-------------------------------+---------+----------------------------------------------+

No of records in user_accounts : around 3000000
No of records in user_details : around 3400000
No of records in user_accounts_user_details : around 2500000

Index in user_accounts

PRIMARY KEY (`id`),
  KEY `idx_accnt_id_del` (`id`,`deleted`),
  KEY `idx_accnt_name_del` (`name`,`deleted`),
  KEY `idx_accnt_assigned_del` (`deleted`,`assigned_user_id`),
  KEY `idx_accnt_parent_id` (`parent_id`),
  KEY `idx_oss_name` (`id`,`name`(15))
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Index in user_details

PRIMARY KEY (`id`),
  KEY `idx_cont_last_first` (`last_name`,`first_name`,`deleted`),
  KEY `idx_contacts_del_last` (`deleted`,`last_name`),
  KEY `idx_cont_del_reports` (`deleted`,`reports_to_id`,`last_name`),
  KEY `idx_reports_to_id` (`reports_to_id`),
  KEY `idx_del_id_user` (`deleted`,`id`,`assigned_user_id`),
  KEY `idx_cont_assigned` (`assigned_user_id`),
  KEY `primary_address_idx` (`primary_address_country`),
  KEY `idx_oss_dt` (`date_entered`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Index in user_accounts_user_details

  PRIMARY KEY (`id`),
  KEY `idx_account_contact` (`account_id`,`contact_id`),
  KEY `idx_contid_del_accid` (`contact_id`,`deleted`,`account_id`)


texts: Changing char(10) to char(3) grew table even after cleantable and shrinkfile

texts:
In a largely experimental attempt to reduce table space usage I altered a char(10) column to char(3)  (The column's contents are never more than three characters long)

Steps (Not carried out in a single batch)

use thedatabase;
alter table thetable
alter column thecolumn char(3) null

DBCC CLEANTABLE (thedatabase,'thetable', 500000);
/* Apparently not needed for fixed length column but just in case*/

DBCC SHRINKFILE (1);

The operation completed successfully, but the table actually grew in size.    I know this can happen due to the way the sql engine modifies a column (creates a copy alongside and then copies the column contents) so I ran dbcc cleantable on the table, and then dbcc shrinkfile on the database file.  None of these operations made any difference to the table size.

The table has no index,  so not possible to run a rebuild index operation.

So my question is,  why did the table grow, even after commands to shrink it.


texts: Debugging random slow query spikes on Index Only Scans

texts:
We have a query that for majority of requests (about 400rpm) executes in around 2ms but occasionally we have one request that will spike to 100ms+. My Googleing may be terrible but does anyone have recommendations around how to debug the cause of these spikes (confirmed it is not network as it appears in PG's slow log) when an EXPLAIN won't help?

Background: server is running 9.5 on RDS on an instance with 15GB (db.r3.large).


texts: Settle debate between Professor and Myself on interpreting Crow's Foot Notation in an ERD

texts:
I am taking an introduction to database objects course at college.

I am in disagreement with the professor over how to interpret the Crow's Foot Notation for Cardinality in an ERD diagram.

Example: Look at this image (one I pulled from lecture slides):

According to my interpretation, this indicates that:

A Supplier may have 0 or many Shipments
A Shipment may belong to only one Supplier
A Customer may have 0 or many Orders
An Order may belong to only one Customer

However, according to my instructor's interpretation, this shows that:

A Shipment may have 0 or many Suppliers
A Supplier can have only one Shipment
A Customer may have only one Order
An Order may have many customers

I have been unable to find any evidence to contract my own understanding of this... but, again, I'm just a student. Maybe I'm missing something? If I am, I'd like to figure out where I went wrong.


texts: Let's imagine I have a legacy application that I would like to connect to a modern SQL Server. This legacy application only understands ASCII, but the SQL Server contains Unicode data in nvarchar column types. Is there an option I can set at the user or connection level to have SQL Server map extended characters back into their nearest ASCII equivalent? E.g. any of à á â ã ã ã in a value returned in a query would be return as plain a, any of è é ê ë would be plain e and so on.


texts: Will table inheritance in PostgreSql suit this situation?

texts:
Let's start off by saying, I'm new to PSQL and coming from a MSSQL background. I'm starting to design a DB in PSQL.

So my main aim is to have a Main schema in this DB, which I am going to use to control certain services with, and have multiple schema's linked to this, which is going to be the different clients data.

I have read up on table inheritance, but have never came across anyone using inheritance with an effect similar to that of DB Migrations.
What I want to achieve is basically have a default schema, with the clients schema's all inheriting from the default. This to me gives control of creating/dropping/altering objects to all schema's by just altering a single schema.

Could this be viable? Has anyone else had any experience doing something similar? Does anyone have any suggestion?

UPDATE

This is what I've done. However, I was under the impression that, if you look at the below code, schema 'client1' will be all I need to do, however I was wrong. :D

So anyway, below is the script I have done, which should take care of everything I need for now - I am going to use the script for the 'client2' schema. Good or Bad?

-- public schema --
CREATE TABLE public.Users (
   Id bigserial primary key,
   Login text unique,
   check (false) NO INHERIT
);

CREATE TABLE public.Roles (
   Id bigserial primary key,
   RoleName text unique,
   check (false) NO INHERIT
);

CREATE TABLE public.UserRoles (
   Id bigserial primary key,
   UserId bigint references users(id),
   RoleId bigint references roles(id),
   unique (UserId, RoleId),
   check (false) NO INHERIT
);

-- client1 schema --
CREATE TABLE client1.Users (
)
INHERITS(public.Users);

CREATE TABLE client1.Roles (
)
INHERITS(public.Roles);

CREATE TABLE client1.UserRoles (
)
INHERITS(public.UserRoles);

-- client2 schema --
CREATE TABLE client2.Users (
  primary key (id),
  Unique (Login)
)
INHERITS(public.Users);

CREATE TABLE client2.Roles (
  primary key (id),
  Unique (RoleName)
)
INHERITS(public.Roles);

CREATE TABLE client2.UserRoles (
  primary key (id),
  foreign key (UserId) REFERENCES client2.Users(id),
  foreign key (RoleId) REFERENCES client2.Roles(id),
  unique (UserId, RoleId)
)
INHERITS(public.UserRoles);


texts: Proactive caching vs. nightly cube reprocess

texts:
I'm working with an SSAS cube that's fully reprocessed every night by an SSIS job.

My current goal is to get data into the cube with less latency, and to do so I'm looking at implementing proactive caching.

Given that proactive caching should update the cube, would there be any benefit to continuing to dump and reprocess the cube periodically, assuming the proactive caching covers everything?


texts: Create SQL Query in function or PROCEDURE

texts:
I am trying to create function inside MySQL to create several queries for example :

    DELIMITER |
    CREATE PROCEDURE queryBuilder(_tableName varchar(100))
    BEGIN
          SET @str_query = 'SET @countRows = 0;SELECT COUNT(*) INTO @countRows FROM';
          SET @str_query = CONCAT(@str_query,_tableName, '; SELECT @countRows');

         PREPARE stmt1 FROM @str_query;

         EXECUTE stmt1;

   END|
   DELIMITER;

but it doesn't work? how can i change to work? I know i can create IF statement but i want something with more flexibility.


texts: Row deletion operation on pg table with numerous foreign keys

texts:
I maintain a Django app with a postgresql backend. Users log in and exchange messages (forum style) with one another.

All users are identified by unique usernames (saved in the auth_user table), that also double up as nicknames for the users when they interact with one another in the forum.

I want to delete/deprecate data of users who haven't logged a session in my web app since the past 3 months. I'm doing this in order to free up nicknames for new users. I.e. new users need to keep a nickname, and if one's taken, they are made to retry. So if I can get rid of old users, the average number of retries a new user has to do will go down too. I.e. it'll become easier for incoming newbies to register for my service. So that's my goal here.

Ideally, I would simply delete the required rows from auth_user, however there are foreign key constraints that get violated (since ON DELETE CASCADE is not set on them, most are DEFERRABLE INITIALLY DEFERRED).

I was going to edit all these foreign key constraints and change them to ON DELETE CASCADE. But before I do that, I wanted to get expert advice on whether that's my only option in the scenario I've described. I'll have to make changes to foreign keys in ~50 tables, and secondly, ON DELETE CASCADE feels a bit too aggressive.


texts:
I have one MySQL table which contains:

a question
4 possible answers separated by a comma
a number (0-3) representing the correct answer.
unique id

with about 2000 entries.

I also have another table of users. The goal of each user is to solve all of the questions on the website.

I need to be able to randomly pick about 30 questions that the user hasn't answered yet.

How do you suggest I should store data about the questions the user has already solved?

At first I thought about a table with | user_id | - | question_id | (so each time the user solves a question correctly a new row will be added), but I don't know how I'll be able to pick 30 rows that he hasn't answered yet..


texts: Select roads that are between FROM and TO

texts:
I have a road table:

 create table road
   (road_id int,
    name_ nvarchar2(15),
    from_ nvarchar2(15),
    to_ nvarchar2(15),
    sort int
   );
insert into road (road_id,name_,from_,to_,sort) values (4,'Street A','Street 1','Street 2','1');
insert into road (road_id,name_,from_,to_,sort) values (6,'Street A','Street 2','Street 3','2');
insert into road (road_id,name_,from_,to_,sort) values (5,'Street A','Street 3','Street 4','3');
insert into road (road_id,name_,from_,to_,sort) values (3,'Street B','Street VI','Street VII','600');
insert into road (road_id,name_,from_,to_,sort) values (7,'Street B','Street VII','Street VIII','700');
insert into road (road_id,name_,from_,to_,sort) values (1,'Street B','Street VIII','Street IX','800');
insert into road (road_id,name_,from_,to_,sort) values (2,'Street B','Street I','Street II','100');
insert into road (road_id,name_,from_,to_,sort) values (9,'Street B','Street II','Street III','200');
insert into road (road_id,name_,from_,to_,sort) values (11,'Street B','Street III','Street IV','300');
insert into road (road_id,name_,from_,to_,sort) values (8,'Street C','Street X','Street Y','9');
insert into road (road_id,name_,from_,to_,sort) values (10,'Street C','Street Y','Street Z','10');
commit;
select * from road order by name_, sort;

   ROAD_ID NAME_           FROM_           TO_                   SORT
---------- --------------- --------------- --------------- ----------
         4 Street A        Street 1        Street 2                 1
         6 Street A        Street 2        Street 3                 2
         5 Street A        Street 3        Street 4                 3

         2 Street B        Street I        Street II              100
         9 Street B        Street II       Street III             200
        11 Street B        Street III      Street IV              300

    {Gap in Street B}

         3 Street B        Street VI       Street VII             600
         7 Street B        Street VII      Street VIII            700
         1 Street B        Street VIII     Street IX              800

         8 Street C        Street X        Street Y                 9
        10 Street C        Street Y        Street Z                10

Eventually, I would like to make a tool that allows users to select roads between a from_ and a to_ (the values would be entered in parameters).

To do this, I think I need to make a query that can do something like:

Select roads where the name_ is Street B, between Street II and Street VIII (inclusive).

   ROAD_ID NAME_           FROM_           TO_                   SORT
---------- --------------- --------------- --------------- ----------
         9 Street B        Street II       Street III             200
        11 Street B        Street III      Street IV              300
         3 Street B        Street VI       Street VII             600
         7 Street B        Street VII      Street VIII            700

How can I do this?


texts:
I'm trying to get a list of unused procedures across the whole SQL server. I've accomplished getting a list of ALL procedures and now I'm using the following script to identify which procedures have been used.

DECLARE @command varchar(1000)
SELECT @command =

'USE [?] SELECT

qt.text AS "SP Name",
qs.execution_count AS "Execution Count",
qs.max_logical_reads,
qs.max_logical_writes,
qs.creation_time,
DB_NAME() as DatabaseName,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS "Age in Cache"
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qt.text DESC'

EXEC sp_MSforeachdb @command

I'm aware that this will only show procedures which have been used post restart. The data seems to work somewhat - however; the issue i'm having is that it seems to show the WHOLE procedures text IE:

  CREATE PROCEDURE dbo.proc_MSS_PropagationGetQueryServers  AS
  DECLARE @Temp table(          ServerID int,          SearchServerName
  nvarchar(31),          IndexDirectory nvarchar(260),
  MachineStatus int,          LastPropagationTime datetime,
  PropagationStatus int      )        INSERT INTO          @Temp
  SELECT          SearchServerID,          SearchServerName,
  IndexDirectory,           MachineStatus,          LastPropagationTime,
  1      AS           PropagationStatus      FROM
  dbo.MSSPropagationSearchServerTable        UPDATE          @Temp
  SET          PropagationStatus = 0      WHERE          NOT EXISTS
  (SELECT * FROM dbo.MSSPropagationSearchServerReady AS R WHERE ServerID
  = R.SearchServerID) AND          (1 < DATEDIFF(m, LastPropagationTime, GETUTCDATE()) OR          30 < DATEDIFF(s, LastPropagationTime,
  GETUTCDATE()))        CREATE TABLE #UnresponsiveQueryServers (
  ServerID int )      INSERT #UnresponsiveQueryServers          EXEC
  dbo.proc_MSS_PropagationGetUnresponsiveQueryServers        UPDATE
  @Temp      SET          PropagationStatus = 3      WHERE
  ServerID IN ( SELECT ServerID FROM #UnresponsiveQueryServers )
  UPDATE          @Temp      SET          PropagationStatus = 2
  WHERE          MachineStatus = 0 OR          MachineStatus = 1 OR
  MachineStatus = 2        SELECT           SearchServerName,
  IndexDirectory,          MachineStatus,          PropagationStatus
  FROM           @Temp

I just want the name of the procedure itself. So in this case  proc_MSS_PropagationGetQueryServers

Anyone able to help on this?


texts: How do I model data from arbitrary CSV's in a database?

texts:
I am setting up a web interface for data management.  Users upload CSV or similarly structured files, and I want to store these in the database so they can do manipulations on them -- filtering, sorting, graphing, etc.

I do not know how to model this properly in a database.  I have several ideas, but none seem to be the right way to do it.

Make a new table for each uploaded CSV. This would mean each column could be appropriately typed (integers, strings, dates, etc.) and each record would trivially correspond to a line in the CSV file.  This seems like a natural conceptualization of the problem -- but would performance become an issue if I had to make a new table for each uploaded file?
Make a table where each record represents a dataset (CSV) and have other tables where data points have the id their dataset in their record. This would mean that all the data from a given dataset is spread across different tables and there would be lots of redundancy (since each data point would store the id of the dataset). However, it would mean tables would not have to be created per-dataset.
Other variations on 2. Most of my other thoughts were variations on number 2 with various amounts of indirection.

My question is essentially "How do I model this properly?", that is, with the ability to scale reasonably.

Most of the data will be scientific, so how do I deal with many data sets of sizes varying from trivial (say, 10 columns and 100 rows) to massive (hundreds of columns and thousands/millions of rows)?

tl;dr: How do I model arbitrary data from an arbitrary number of well-formed CSVs in a database, and would a new table for each CSV perform acceptably?


texts: Is it possible in Firebird to create a single index with ascending and descending columns?

texts:
For instance, something equivalent to

CREATE INDEX Listing_Date_Idx ON ACTIVITY(Listing,Date_ DESC,Time_ DESC);


texts: Effect of --flush-logs on a slave mysqldump?

texts:
I have this database setup:

Master -> Slave A

I would like to accomplish the following:

Master -> Slave A -> Slave B

I have reviewed the documentation.

Master and Slave A are both in an MSP environment, while Slave B will be an RDS instance. I can connect to Slave A in the MSP just fine. Master binlog files are being copied over to the instance running Slave A. All binlog files are being kept around for a long time.

However, my database size is 1.3 TB. Thus, a mysqldump on Slave A takes several hours to complete, and even longer to load into my new empty Slave B before beginning replication. In the best case scenario, it takes ~36 hours from starting the dump to when replication can start.

I would like to use --flush-logs, as described in the third code snippet here, to reduce the chances of having to use CALL mysql.rds_skip_repl_error; repeatedly after I begin replication. This is especially important because there is currently no way to accomplish the equivalent of slave-skip-errors = 1062 in RDS, and also because I'm unsure what the effect is on data consistency.

Is my assumption correct, that using --flush-logs reduces the likelihood of duplication errors arising after replication has begun?

If it is correct, what would the effect be of using mysqldump --single-transaction --flush-logs on Slave A? Would it behave as a tunnel and actually flush the logs on Master, or would it only affect the logs present on Slave A?


texts: Declare variable of table type in PL/pgSQL

texts:
I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like:

q1 = select * from foo;
q2 = select * from bar;
for t1 in q1:
  for t2 in q2:
    -- do something with t1 and t2

I looked into the return next construct but that seems to only able to handle return values.


texts: How can you create a ROLE which isn't allowed to do anything?

texts:
I want to create a role which by default isn't allowed to do anything. It has basically zero permissions. Starting from such role, I want to grant the minimal set of permissions.

How can you make a role without any kind of permissions?


texts:
I have a table which looks like this:

Messages table:

Message_ID (BIGINT) PK auto_increment
FromUser (INT 16)
ToUser (INT 16)
DateCreated (Timestamp)
MessageText (Varchar (500) )
HasRead (TINYINT (0=false, 1=true) )

The queries which will be run will include:

SELECT *
FROM messages
WHERE FromUser = '10000000'
ORDER BY DateCreated DESC
LIMIT 10;

SELECT *
FROM messages
WHERE ToUser = '10000000'
ORDER BY DateCreated DESC
LIMIT 10 ;

UPDATE messages
SET HasRead = 1
WHERE Message_ID = '123456789'; // When a message has been read update it to show it has been read.

As a result I have created the following indexes:

Message_ID (primary index)
(FromUser,DateCreated) BTREE DESC
(ToUser,DateCreated) BTREE DESC

I have the following conclusions:

The MessageID primary index is used for the update operation.
The composite FromUser andDataCreated index are is used to get all the messages from a user. My question here is if this is a proper index? Can I just include FromUser or do I need both if I am also sorting (User can have a lot of messages)? If I drop DateCreated using explain I can still see it uses this index, but then it seems to use filesort to sort; is this efficient or is it better to have a larger index and use the index to get the sorted values?
The composite ToUser and DataCreated index are is used to get all the messages  a user sent. My question here again is if this is a proper index? Can I just includeToUser or do I need both if I am also sorting (User can have a lot of messages)? If I drop DateCreated using explain I can still see it uses this index, but then it seems to use filesort to sort; is this efficient or is it better to have a larger index and use the index to get the sorted values?

These are chat messages so they will accumulate quickly. Would it be wise to partition as well? If so would what would be a good partition strategy? If I partition a range on from users it will work great as a users messages will be stored on the same partition but to users won't be on the same partition. Vice versa also, so I can't find a good partition key


texts: Oracle Ref Partitioning: Deadlock due to child table row migration

texts:
My ordering application uses Oracle 11g Database. This DB has a primary table ORDERS and multiple child tables like ORDER_DETAILS, PLAN etc.

ORDERS table is LIST partitioned on STATUS column and all other tables are referenced partitioned with ORDERID as a foreign key.

At peak load, when order status is changed and ORDERS table row is moved from one partition to another, Oracle performs row migration for all the child tables referenced partitioned by ORDERS table. Due to many tables that depend on ORDERS table, large number of row movements happen causing a deadlock in one of the child table.

My question is, how to resolve a deadlock caused in the ORACLE's internal row migration step?

Here is an example setup:

ORDERS table:

CREATE TABLE ORDERS (
       orderID   NUMBER PRIMARY KEY,
       description    VARCHAR2(30),
       status   VARCHAR2(30))
  PARTITION BY LIST (status) (
       PARTITION A VALUES ('COMPLETED'),
       PARTITION B VALUES ('ACTIVE'),
       PARTITION C VALUES ('SUBMITTED'))

Child table: PLAN

CREATE TABLE PLAN (
       planID   NUMBER PRIMARY KEY,
       orderID    NUMBER,
       description   VARCHAR2(30),
       CONSTRAINT FKC64393AD1EC7235 FOREIGN KEY (orderID)
       REFERENCES ORDERS (orderID) ON DELETE CASCADE
 )
 PARTITION BY REFERENCE (FKC64393AD1EC7235);

There are many more child table where ORDERS is there parent table.

Under heavy load, when ORDER status is changed which causes row movement between partition, following deadlock error is printed in the log
ORA-00060: deadlock detected while waiting for resource

In the Oracle trace log, I see following SQL causing deadlock

update /*+ opt_param('_and_pruning_enabled', 'false') */ "TEST"."PLAN" partition (dataobj_to_partition( "TEST"."ORDERS" , :1)) move to partition (dataobj_to_partition( "TEST"."ORDERS" , :1)) set "ORDERID" = "ORDERID" where "ORDERID" = :1

Now this SQL is internally generated by Oracle to perform row migration for child PLAN table.

To resolve the issue I tried following changes:

I have confirmed that OrderID column (Foreign Key column) in the PLAN table has index on it.
Tried increasing PCTFREE parameter on the table.

But havnt got success yet. How do I handle deadlock for this scenario?

------------------------- UPDATE -------------------------

As per suggestions suggested by Wernfried and Gandolf989, I verified if all my foreign keys have indexes on them by running query given in the Gandolf989 answer.

Result was "No Rows Found". So it means, all the indexes seems to be in place. But while analyzing I realized, if I check an explain plan for a simple query like below, I see FULL table scan on the PLAN table even after having an index on ORDERID column.

 UPDATE PLAN SET DESCRIPTION = 'ABC' WHERE orderid= '234';

Following is the index statement originally run on this table.

 CREATE INDEX IDX_PLAN_ORDERID ON  PLAN(ORDERID);

So for some reason, ORACLE is not taking Index into consideration while running update query on PLAN table. Am I missing something?


texts: Cannot Start Oracle Database after Shutdown

texts:
I have install oracle 11.2.0.4 with oracle asm on oracle linux 6.4 and it work correctly .
After shutdown abort , i can not start database at all .
can any one help me ?

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 6 14:14:30 2015

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

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORA-27137: unable to allocate large pages to create a shared memory segment
Linux-x86_64 Error: 1: Operation not permitted
Additional information: 201326592
Additional information: 1
SQL>

--------------------------------------------
[grid@localhost ~]$ asmcmd
ASMCMD> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
-------------------------------------------
[root@localhost ~]#  grep Huge /proc/meminfo
AnonHugePages:    100352 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB


texts:
I just started my first Database course online, and I have a homework assignment to create an entity-relationship diagram (ERD) from a list of specifications.

The specs are as follows:

The company is organized into departments. Each department has a name, an unique number, and a particular employee who manages the department. We keep track of the start date when the employee began managing the dept. A dept may have several locations.
A dept controls a number of projects, each of which has a name, unique number, and single location.
We store each employee’s name, SSN, address, salary, sex, and DOB. An employee is assigned to one dept but may work on several projects, which are not necessary controlled by the same dept. We keep track of the number of hours per week that an employee works for each project. We also keep track of the direct supervisor of each employee.
We want to keep track of the dependents of each employee for insurance purpose. We keep each dependent’s first name sex, DOB, and relationship to the employee.


texts: Equivalent of old style join with new style

texts:
Can someone please help me write the following join expression in new style:

Select tb1.col1,tb2.col2
  from table1 tb1, table2 tb2
 where tb1.col1>=tb2.col2

I know that the equivalent of

from table1 tb1,table2 tb2
 where tb1.col1=tb2.col2

is

from table1 tb1 inner join table2 tb2 on tb1.col1=tb2.col2

but I don't know how to do when it is >=  and not just =


texts:
I am using AWS RDS PostgreSQL and currently up and running. I would like to know how I can add a new user but only grant SELECT on an existing DB.

The way I connect to my RDS from my PC is by using a app called pgAdmin III (from Ubuntu).

I tried to create a group called rds_guest, assign a user (called webkul) and finally GRANT privileges (INSERT, SELECT, UPDATE etc) on the database I want for that particular group:

When I try to login using that user and try perform actions on the desired database I get an error saying:

  Permission denied for relation xxx_yyy

Can anyone help me with this ? I am relatively new with Postgres.


texts: Distinguishing Records with Related Fields

texts:
I'm a newer user (obviously) and would greatly appreciate question writing suggestions as well as answer suggestions:
I have a lot of interrelated tables which largely depend on my Products table, which is the master list of a certain product. Much of the information for any given product is determined by traits from the Shipments table. There is a one-to-many relationship between Shipments and Products. Each shipment has a Product Version field, but in addition to having a unique version, any shipment may be a trial shipment.
Which is the better design?
There are two workable designs which come to mind.
1. Current Design
The Product Version field contains values for each possibility. For example, a product of version 7.6 would have the value 7.6, but a 7.6 product which is a trial would be 7.61. To select all 7.6 products, I would use a Like "7.6*" statement. This keeps all of the versions in one table and I have this documented for the next user. The irritating thing is that I have to have a field containing the necessary SQL to query that specific version. This SQL is located in a ProductVersion table.
2. Adding a Field
I could have a True/False field for each shipment which indicated if it was a trial... (I also have to take into account the ease with which new users will be able to use this without me, but as that's a bit subjective, that's not the core question.)
I don't know if this makes a difference but
Part of the fun with my current setup is that only some records in Products have associated Shipments, since for sometime the shipments were not recorded. The shipments are also imported into the database in a different process, sometimes after the associated product.
So:
Is it a better design to add a boolean field for trial products?


texts: How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?

texts:
I have to create a xls datafeed for a website and I would like to know which tables are getting affected when I do a manual entry from CMS.

If i have installed fresh database and I'm doing first entry in it using CMS: I would like to know which tables got updated/appended in last 1 min in that DB.

It is somewhat similar to this question https://stackoverflow.com/questions/307438/how-can-i-tell-when-a-mysql-table-was-last-updated

But in my case I dont know which tables to check. I can check each and every table in the db using the solution posted in the question but I have a gut feeling that there is a better solution for this.


texts: query to check all SCD tables that have data for specific date

texts:
It's easy to find all SCD tables through

select
    *
from
    syscat.TABLES t
    join
    syscat.COLUMNS s
    on t.tabname = s.tabname
where
    s.COLNAME = 'EFFECTIVE_DTE' and t.type='T'
    with ur;

problem is how to further apply SQLs on the resut tables sets and check if each contain data for specific date ? say:

select effective_dte, count(*) from TABLE1 group by TABLE1 where ...

is it possible to futher apply the queries to all result tables


texts: Server Doesn't Listen Error when connecting to PostgreSQL from Client

texts:
I have done a lot of research through the Google and have not been able to come up with a solution. I installed PostgreSQL 9.5 and PostGIS 2.1.8 On Ubunutu Server 14.04 and configured PostgreSQL as per this tutorial. In genral the tutorial says to:

Configure pg_hba.conf to include host all all 192.168.1.0/24 md5; obviously inserting my ip address and I wanted password verification.

 # DO NOT DISABLE!
 # If you change this first entry you will need to make sure that the
 # database superuser can access the database using some other method.
 # Noninteractive access to all databases is required during automatic
 # maintenance (custom daily cronjobs, replication, and similar tasks).
 #
 # Database administrative login by Unix domain socket
 local   all             postgres                                peer
 # TYPE  DATABASE        USER            ADDRESS                 METHOD
 # "local" is for Unix domain socket connections only
 local   all             all                                     peer
 # IPv4 local connections:
 # host    all             all             127.0.0.1/32            md5
 host    all             all             192.168.1.0/24          md5
 # IPv6 local connections:
 host    all             all             ::1/128                 md5
 # Allow replication connections from localhost, by a user with the
 # replication privilege.
 #local   replication     postgres                                peer
 #host    replication     postgres        127.0.0.1/32            md5
 #host    replication     postgres        ::1/128                 md5`
Configure postgresql.conf to include listen_addresses='*'
Configure iptables firewall rules; I did this as follows:

I restarted networking and PostgreSQL, then attempted to connect through PgAdmin III on a Windows Client and a Server Doesn't Listen Error

The server PostgreSQL log is as follows:

2016-01-18 14:45:04 EST [7510-2] LOG:  MultiXact member wraparound protections are now enabled
2016-01-18 14:45:04 EST [7509-1] LOG:  database system is ready to accept connections
2016-01-18 14:45:04 EST [7514-1] LOG:  autovacuum launcher started
2016-01-18 14:45:04 EST [7516-1] [unknown]@[unknown] LOG:  incomplete startup packet
2016-01-18 15:42:17 EST [7509-2] LOG:  received fast shutdown request
2016-01-18 15:42:17 EST [7509-3] LOG:  aborting any active transactions
2016-01-18 15:42:17 EST [7514-2] LOG:  autovacuum launcher shutting down
2016-01-18 15:42:17 EST [7511-1] LOG:  shutting down
2016-01-18 15:42:17 EST [7511-2] LOG:  database system is shut down
2016-01-18 15:42:18 EST [7802-1] LOG:  database system was shut down at 2016-01-    18 15:42:17 EST
2016-01-18 15:42:18 EST [7802-2] LOG:  MultiXact member wraparound protections are now enabled
2016-01-18 15:42:18 EST [7801-1] LOG:  database system is ready to accept connections
2016-01-18 15:42:18 EST [7806-1] LOG:  autovacuum launcher started
2016-01-18 15:42:18 EST [7808-1] [unknown]@[unknown] LOG:  incomplete startup packet
2016-01-18 18:56:51 EST [7801-2] LOG:  received fast shutdown request
2016-01-18 18:56:51 EST [7801-3] LOG:  aborting any active transactions
2016-01-18 18:56:51 EST [7806-2] LOG:  autovacuum launcher shutting down
2016-01-18 18:56:51 EST [7803-1] LOG:  shutting down
2016-01-18 18:56:51 EST [7803-2] LOG:  database system is shut down
2016-01-18 18:56:53 EST [10519-1] LOG:  database system was shut down at     2016-01-18 18:56:51 E$
2016-01-18 18:56:53 EST [10519-2] LOG:  MultiXact member wraparound protections are now enabled
2016-01-18 18:56:53 EST [10518-1] LOG:  database system is ready to accept connections
2016-01-18 18:56:53 EST [10523-1] LOG:  autovacuum launcher started
2016-01-18 18:56:53 EST [10525-1] [unknown]@[unknown] LOG:  incomplete startup packet

I am not sure what I am missing.FYI, I am generally a Ubuntu noob. I know enough to be dangerous, so please, if you are willing think laymen terms.


texts: Ghost Cleanup process has high CPU usage in SQL Server 2012 SP1

texts:
Has anyone encountered this?  We are using SQL Server 2012 Enterprise Edition with SP1.  During a time of low/no activity, I noticed the Ghost Cleanup process is utilizing CPU at a very high rate, from 75% to 99%, these aren't bursts but sustained usage.

I don't see the ghost cleanup process blocking itself, it just seems to take a lot of CPU while it runs, and never seems to complete.  How can I tell if it's actually doing something?

Is this a bug with SQL Server 2012?  I have seen this in SQL 2008 and there was a fix for it but I haven't seen anything for 2012.


texts: Add column with default in postgresql without table level lock

texts:
Have such a problem - table with over 20mln rows.

When i add new column with default - postgresql lock table for over 40 minutes so my application stop working for this time.

So instead of

ALTER TABLE "test" ADD COLUMN "field" boolean DEFAULT True NOT NULL;

I do

ALTER TABLE "test" ADD COLUMN "field" boolean NULL;
ALTER TABLE "test" ALTER COLUMN "field" SET DEFAULT true;

after which every new row will be with true by default, so now i need to update 20mln current rows.
I update them in batches:

WITH cte AS (
SELECT id as pk
FROM "test"
WHERE  "field" is null
LIMIT  10000
)
UPDATE "test" table_
SET "field" = true
FROM   cte
WHERE  table_.id = cte.pk

after which i do

ALTER TABLE "test" ALTER COLUMN "field" SET NOT NULL;

And everything is allwright but step when i update rows is so much slow. Can you give my some advice about improving speed of update?

Currently it update 10000 in about 2 minutes.
I try decreasing size to 1000 - it was better (3.5 minutes for 10000) but it still to slow.
I also try create index before update, but it doesnt give better results (as i understand it will give better result but when huge path of table will be updated).


texts: What is the differences between Central Management Servers and Master Server (MSX) / Targer Server (TSX) concepts in SQL Server? I know that MSX/TSX is related to SQL Agent and the basic idea is to run jobs from MSX on TSX (possibly many), but I thought this is also possible in CMS scenario (by using scheduled stored procedures). Is that so?


texts: VARCHAR memory buffer appears to be breaking in ChineseTaiwan Collation

texts:
It is difficult for me to write something in English. I hope you understand.
I used a translator and reviewed it.

I created two temporary databases. The collations are 'Korea_Wansung_CI_AS' and 'Chinese_Taiwan_Stroke_CI_AS' respectively.

I created a temporary table with a column of type VARCHAR(2048) and I just created a procedure using INSERT for this table data here.

When invoking the stored procedure from a server that uses c++ oledb, the VARCHAR value is encrypted and sent as UTF-8.

We sent

'한국어' -> 'Korean_Wansung'¨
'韓國人學台' -> 'Chinese_Taiwan'.

I have verified through debugging that the value is encrypted with normal UTF-8.

'한국어' -> UTF-8 -> '\xed\x95\x9c\xea\xb5\xad\xec\x96\xb4'
'韓國人學台' -> UTF-8 -> '\xe9\x9f\x93\xe5\x9c\x8b\xe4\xba\xba\xe5\xad\xb8\xe5\x8f\xb0'

Here I have a problem.

When the table was read back via SELECT statement in the database, we found that for Korean_Wansung, the string 'matched by the value of the Code Page 949' appears normally.

But not for Chinese_Taiwan. The value stored in memory itself appears to be broken.

If you take the value from the server and decode it, the original character will appear normal for Korean_Wansung.

But in the Chinese case, it doesn't come out normally.

I need your help.

If you need additional information, we will quickly get back to you and upload it.

Thank you.


texts:
We are using Ola's Maintenance solution and part of that we have job DatabaseIntegrityCheck - User Database and System Databases, sometimes job for some of the databases it's running longer like in days and sometimes also it's skipping the DB
We have Sql server 2012 (Sql Server version is 11.0.5388.0 -Standard) and database is not that much big like under 10GB.
Someone's help really appreciated!

Added:

MAster DB is only 5 MB
Ola command:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d AdminDB -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y'" -b
 DBCC CHECKDB ([master]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

Script is not blocked, it's running for longer for Master and another user DB
Other Databases ran fine.


texts: Alternatives to AdeptSQL Diff (for MS SQL Server 2012)

texts:
It seems AdeptSQL Diff is not compatible with SQL Server 2012 (at this time). And it is my preferred tool for deploying changes. I realize this is a shopping list but I hope SE won't mind.

I am wondering what are the best alternatives out there and wanted to query the DBAs.

Currently I am evaluating RedGate SQL Compare. It doesn't include data comparison (separate product) though it does generate deployment scripts which is nice. Just want something quick and painless for launching schema changes to production servers. What do you recommend. Thanks!


texts: How can I have SQL Server email me the error details when a job fails?

texts:
SQL Server lets you configure a job to send email alerts when it fails.  This is a simple and effective way to monitor your jobs.  However, these alerts do not include any detail--just a success or failure notice.

If a job fails, this is what a typical alert email will look like:

JOB RUN:        'DBA - Consistency Check Databases' was run on 8/14/2011 at 12:00:04 AM
DURATION:       0 hours, 0 minutes, 0 seconds
STATUS:         Failed
MESSAGES:       The job failed.  The Job was invoked by Schedule 2 (Nightly Before
                Backup 12AM).  The last step to run was step 1 (Check Databases).

To determine the cause of the failure, you have to navigate to the instance in SQL Server Management Studio, find the job, and view its execution history.  In a large environment it can be a pain to have to constantly do this.

The ideal alert email would include the failure reason upfront and let you get straight to working on the solution.

I am familiar with this solution to this problem.  Does anyone have any experience with it?  Its drawbacks are:

you have to add a new step every job you have, and
you have to pray that no-one messes up the alert proc, spDBA_job_notification

Has anyone come up with a better solution?


texts: Summing Column values based on group by criteria

texts:
So I have this query

    select *, TIMEDIFF(tbltimetracker.tt_endtime, tbltimetracker.tt_begintime)
as hours from tbltimetracker
          LEFT JOIN tblcustomers
on tbltimetracker.customer_id = tblcustomers.customer_id
          LEFT JOIN tblusers
on tbltimetracker.user_id = tblusers.user_id
          WHERE tbltimetracker.owner_id = 38
          LIMIT 0, 50'

My intent is to group these by customer and sum the hours based on that. So full disclosure is I have a client who wants to import their payroll and their payroll company needs the excel sheet in a certain format which is like this but the only difference is, employees (users) hours are totaled by the customers (or locations) that they worked at. So for instance, currently it's:

Payroll Id | User Name | Customer  | Hours |
5C           Tom         S. County   5
10C          Sam         N. County   3
5C           Tom         S. County   4
5C           Tom         N. County   7

But it should but

Payroll Id | User Name | Customer  | Hours |
5C           Tom         S. County   9
10C          Sam         N. County   3
5C           Tom         N. County   7

This is what I've come up with

    select *, SUM(TIMEDIFF(tbltimetracker.tt_endtime, tbltimetracker.tt_begintime))
as hours from (select * from tbltimetracker where tbltimetracker.customer_id = tblcustomers.customer_id)
          LEFT JOIN tblcustomers
on tbltimetracker.customer_id = tblcustomers.customer_id
          LEFT JOIN tblusers
on tbltimetracker.user_id = tblusers.user_id
          WHERE tbltimetracker.owner_id = 38
          LIMIT 0, 50'

But this does not work, I hope it gives you an idea though.


texts:
I am designing a Tournament Schema for Scrabble tournaments, including Leagues and Rounds for each Tournament. The following is the initial schema design we have.

A Tournament has many leagues, a league has many rounds, and a round has many games. What we can't work out is where we store the players without generating many-to-many relationships.

We're looking for ways in which we can improve the design from here as I'm not sure this is going to work as is.


texts: I just exited SSMS while restoring a UAT database, am I screwed?

texts:
I just flinched and closed out of habit while waiting for the restore to reach 100%. Did I just break everything? Is it possible to view the progress of the restore?

Update:  I was in a bit of a panic, so I roped in my team lead. The database was now stuck in the restoring state and that script didn't work so we just dropped it and started over. It went okay.

Was that necessary? In the UI for a restore there is a period of time before the percentage complete is displayed. I had closed the application before that... not sure if that matters.


texts: DB2 - how to call a stored procedure that returns a result set in another user defined table function

texts:
I have a db2 stored procedure that takes in some parameters, gets some data from somewhere and then returns a result set through a cursor.

Now I want to write a table function in db2, that will call this stored procedure, read from the result set and return the data in the result set as a table (eventually I want to use this table function in a join).

I would like to know if this is permitted in db2 (we're using DB2 v10.5), i.e. execute a stored procedure in a table function and fetch and read from the result set from the stored procedure.
If so, what is the right syntax for calling the stored procedure and reading the result set inside a table function in db2?
Thanks!


texts: Postgres: How to insert row with autoincrement id

texts:
There is a Table "context".
There is an autoincrement id "context_id".
I am using sequence to retrieve the next value.

SELECT nextval('context_context_id_seq')

The result is: 1, 2, 3,...20....

But there are 24780 rows in the "context" table

How can I get the next value (24781)?

I need to use it in the INSERT statement


texts: How to capture execution plan from sql profiler for filtered queries only

texts:
Using sql profiler, we trace all slow queries (filter on duration/reads) to see where we can optimise.
Events used:

RPC:Completed
SQL:BatchCompleted

Filter on Duration.
If I add the event ShowPlan XML, then I cannot filter on the duration of the underlying query, creating a huge load as hundreds/thousands of queries arrive each second
How to only capture the execution plan for the entries that match the filter duration/reads of the captured queries from the other events?


texts: Constant writing operations on google cloud database

texts:
I have a MySQL 8.0 cluster database and so far I'm using it to test a couple of things, so I only write from time to time. Problem is that I have realised that there are write operations all the time. Even if my apps are not writing to it.
This is a screenshot of the dashboard.
Just to mention, I have another cluster in Digital Ocean and this issue is not happening there.
Any help will be appreciated

UPDATE (2021-03-09)
Executing query as Vérace suggested:

As I see that time_zone_transition is the one growing the most


texts: How do you write to a folder with spaces in it?

texts:
I've got a bunch of simple SSIS packages that output SQL data to excel, then I rename the files and move them out to the end user.

Some of them use file paths with spaces in them, and they aren't excited about using new folders.  Here's the code I'm using:

    declare @date varchar(25),
    @sql varchar(1000)

    set @date = cast(datepart(month, current_timestamp) as varchar(2)) + '_' +  cast(datepart(day, current_timestamp) as varchar(2)) + '_' +  cast(datepart(year, current_timestamp) as varchar(4))
    print @date

    set @sql = 'copy D:\Data\ED_72.xlsx \\ehsintra3\ED_72_weekly\ED_72_' + @date + '.xlsx';
    print @sql

    exec  xp_cmdshell @sql

but it wouldnt work without the underscores.

Question how can I make this work without underscores?


texts: How To Roll Back DR Transactions Without Breaking Mirror

texts:
We have a full data center DR test coming up and will need to fail over all of our SQL Server databases to the DR site.  The majority (>80%) of our databases are SQL Server 2008 R2 using Database Mirroring for DR.  The test duration is 56 hours.  We have requirements to

maintain production availability during the test,
roll back any changes made during the test and
resume normal operations (including re-establishing mirroring) within the time window allowed for the test.

The biggest issue we have is that we will need to break the mirror to allow both production and DR to be available for read/write.  That forces us to take backups and copy them to DR to resume mirroring.

There are ~2,000 databases involved with over 4 TB of backup files.  At those volumes it would not be possible to resume mirroring within the time frame allowed.

Does anyone have any ideas or know of any third-party software that would meet the requirements set down for this test?

I know Oracle has a Flashback feature which allows administrators to manually set a recovery point and roll back to that point when ready.  Is there anything in SQL Server that could let us do that?


texts:
I have a query that I want to get data from the past 5 quarters, but it doesn't work the way it should:

select to_char(eventdate,'YYYY') || ' Q-' || to_char(eventdate, ' Q'), ...
from ...
where eventdate between (sysdate - 458) and sysdate

It doesn't do what I want. How do I make sure I get all rows for the current quarter and the last 4, no matter what day of the quarter it is?


texts:
I have something like:

SELECT a, b
INTO v_a, v_b
FROM (
    SELECT x, y
    FROM whatever
    UNION
    SELECT z, w
    FROM something
) AS subquery (a, b)

Which of course doesn't work in Oracle (that syntax - apart from the INTO and the optional AS) would be fine in SQL Server http://sqlfiddle.com/#!6/b7dcf/1

Is there an alternative to this (which requires me to put the aliasing inside) where the column aliasing is outside:

SELECT a, b
INTO v_a, v_b
FROM (
    SELECT x AS a, y AS b
    FROM whatever
    UNION
    SELECT z, w
    FROM something
) subquery;

http://sqlfiddle.com/#!6/c12e1/1 - which works on both SQL Server and Oracle, of course

My objection to the interior aliasing is that it is not as good for maintenance, obviously, if new sections are added above that section, the aliasing gets whacked.


texts:
Tables: Payments with auto-increment ID and one-to-many relationship to Projects such that one payment (say £10) has two records in Projects allocating the £10 between the projects.

Now next month, this payment is received again and I would like to duplicate this payment and its project's records except for changing the dates.

With one table you can

 INSERT INTO Payments (date, amount)
 SELECT CURRENT_DATE date, amount
 FROM Payments
 WHERE id IN (1,4,5,6,123) /* e.g. user-selected set of paymeents */
 ...;

Obviously to re-create the child rows you need the new insert ID from Payments to use for the PK of the child rows.

Is there a way to do this in (MySQL) SQL?

I'm doing this for a whole set of matches, not just one. I'm looking for a more efficient way than reading it all out into -say- PHP and doing it in a loop that way.

I've been asked for the CREATE TABLEs. They're a simple example, but here you go:

CREATE TABLE `payments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `amount` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `projects` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `payment_id` int(10) unsigned NOT NULL,
  `project` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `payment_id` (`payment_id`),
  CONSTRAINT `y_ibfk_1` FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts: How to get first and last value by same item in sequence, with possibility of the same item occurring again

texts:
Similar to this question but with the possibility that the same name can appear again in the following rows.

I have this table:

id name     value
1  Daniel   3
2  Daniel   7
3  Daniel   2
4  Carol    9
5  Carol    4
6  Carol    9
7  Ray      5
8  Charles  1
9  Daniel   6
10 Daniel   5
11 Daniel   5

I would like to get the first and the last value (ordered by id) in a table like:

name    value_1st  value_last
Daniel  3         2
Carol   9         9
Ray     5         5
Charles 1         1
Daniel  6         5


texts: Generate `insert` statements for each entry in a table

texts:
What is the simplest way to generate insert statements fro every entry in a table? For a table with 3 rows, I need to generate 3 insert statements.

For a table with n rows I need to write to a file n insert statements.

For example, for row (foo, bar, baz) I need to write to that file the statement:

insert into desired_table values (foo, bar, baz)


texts:
We have a weekly maintenance plan that does your typical Integrity Checks and then goes and does an Update Statistics on all the databases on our server.

Over the weekend it appears that our job was hung up on the Update Statistics (with full scan) job. Looking at the Activity Monitor it appeared to have a Task State of SUSPENDED for the command Update Statistics. And for a Wait Type was LCK_M_X.

My Question: Why would it be suspended? A lock on a table in the database it was doing statistics on couldn't have stopped it? What would hang an update statistics?


texts:
My mysql box keeping shutting down and up. Below is snippet of the log file. There is quite a number of places I notice this Forcing close of thread 946  user:

130426 12:36:28 [Note] Event Scheduler: Purging the queue. 0 events
130426 12:36:28  InnoDB: Starting shutdown...
130426 12:36:31  InnoDB: Shutdown completed; log sequence number 0 1111309
130426 12:36:31 [Note] /usr/libexec/mysqld: Shutdown complete

130426 12:36:31 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
130426 12:36:32 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130426 12:36:32  InnoDB: Initializing buffer pool, size = 8.0M
130426 12:36:32  InnoDB: Completed initialization of buffer pool
130426 12:36:32  InnoDB: Started; log sequence number 0 1111309
130426 12:36:32 [Note] Event Scheduler: Loaded 0 events
130426 12:36:32 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.67'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
130426 21:05:19 [Note] /usr/libexec/mysqld: Normal shutdown

130426 21:05:19 [Note] Event Scheduler: Purging the queue. 0 events
130426 21:05:21 [Warning] /usr/libexec/mysqld: Forcing close of thread 946  user: ''

130426 21:05:22  InnoDB: Starting shutdown...
130426 21:05:23  InnoDB: Shutdown completed; log sequence number 0 1111309
130426 21:05:23 [Note] /usr/libexec/mysqld: Shutdown complete

130426 21:05:23 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
130426 21:05:25 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130426 21:05:25  InnoDB: Initializing buffer pool, size = 8.0M
130426 21:05:25  InnoDB: Completed initialization of buffer pool
130426 21:05:25  InnoDB: Started; log sequence number 0 1111309
130426 21:05:25 [Note] Event Scheduler: Loaded 0 events
130426 21:05:25 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.67'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
130428  0:47:12 [Note] /usr/libexec/mysqld: Normal shutdown

130428  0:47:12 [Note] Event Scheduler: Purging the queue. 0 events
130428  0:47:12  InnoDB: Starting shutdown...
130428  0:47:16  InnoDB: Shutdown completed; log sequence number 0 1111309
130428  0:47:16 [Note] /usr/libexec/mysqld: Shutdown complete


texts:
I am trying to determine the total number of contributors in an image database who have contributed at least five images. The following query will give me counts of images by contributor, but that's only part of the puzzle. I'm sure this is simple to someone who has more knowledge than me :)

SELECT count(*) AS i_count,contributor_id FROM images GROUP BY contributor_id ORDER BY i_count DESC


texts: Store Function Value into temporary table

texts:
Hi i'm currently trying to run a SQL query on a 15 000 + entry dataset. I'm using a custom Mysql function to determine the start of the current week and the end of it. Probleme is, I'm using those values in the WHERE clause of my query so it is executed at each comparaison, which take a lot of time.  I've discovered that those values never change, so I need to execute the function only one time to get the value, I can then pass the value to the WHERE clause.

The function itself does not takes long to execute, it's doing it 60 000 times that takes a long time.

I was wondering if there is a way to save those values into some kind of table where I could check if the start and end of the week has already been calculated and if so, use those value.
 I tried using a temporary table but I would need to know when the query is done so I could delete it.

Here is my code so far :

SELECT TIME_TO_SEC(TIMEDIFF(STR_TO_DATE(otherShifts.sft_end, '%Y-%m-%dT%H:%i:%s.000Z'),
                            STR_TO_DATE(otherShifts.sft_start, '%Y-%m-%dT%H:%i:%s.000Z'))) / 3600 as timestamps
                    FROM shift_sft as otherShifts,
                         shift_sft as originalShift
                    WHERE (
                        otherShifts.sft_start BETWEEN getStartOfWeek(originalShift.sft_id) AND getEndOfWeek(originalShift.sft_id) OR
                        otherShifts.sft_end BETWEEN getStartOfWeek(originalShift.sft_id) AND getEndOfWeek(originalShift.sft_id))
                    AND originalShift.sft_id = 1;

getStartOfWeek :

create function getStartOfWeek(shiftId int) returns varchar(122)
BEGIN
      DECLARE shiftStart varchar(122);
      DECLARE currentUserId INT(11);
      DECLARE currentSunday DATETIME;
      DECLARE currentWeekStart DATETIME;
      DECLARE currentStartingWeekSetting int;
      SELECT sft_start, sft_usr_id INTO shiftStart, currentUserId FROM shift_sft WHERE sft_id = shiftId;
      SET currentSunday :=  DATE_ADD(STR_TO_DATE(SUBSTRING_INDEX(shiftStart, 'T', 1), '%Y-%m-%d'), INTERVAL(1-DAYOFWEEK(STR_TO_DATE(SUBSTRING_INDEX(shiftStart, 'T', 1),  '%Y-%m-%d'))) DAY);
       SELECT FIELD(sde_day_week_start,'sun','mon', 'tue', 'wen', 'thu', 'fri', 'sat')
       FROM setting_default_sde, setting_business_sbu, v_user_business
       WHERE v_user_business.usr_id = 1
       AND setting_business_sbu.sbu_sde_id = setting_default_sde.sde_id
       AND setting_business_sbu.sbu_bus_id = v_user_business.bus_id;
       SET currentWeekStart := DATE_ADD(currentSunday, INTERVAL (currentStartingWeekSetting - 1) DAY);
       RETURN DATE_FORMAT(currentWeekStart, '%Y-%m-%d');
     END;

getEndOfWeek:

create function getEndOfWeek(shiftId int) returns varchar(122)
BEGIN
   DECLARE startOfWeek varchar(122);
   DECLARE endOfWeek DATETIME;
   SELECT getStartOfWeek(shiftId) INTO startOfWeek;
   SET endOfWeek := DATE_ADD(startOfWeek, INTERVAL 7 DAY);
   RETURN DATE_FORMAT(endOfWeek, '%Y-%m-%d');
 END;


texts:
We have a table as below

CREATE TABLE "ABC"(
"ID" NUMBER (18, 0) NOT NULL,
"ID2" VARCHAR2(15 char) NULL,
"VERSION" VARCHAR2(30 char) NULL,
"TIMESTAMP" DATE  NULL,
 CONSTRAINT "PK_ABC" PRIMARY KEY ("ID")
);

ALTER TABLE ABC ADD  CONSTRAINT CK_ABC_ID2_VERSION UNIQUE(ID2, VERSION);

CREATE UNIQUE INDEX IDX1_ABC ON ABC
(
    ID2,
    VERSION
);

CREATE INDEX "IDX2_ABC" ON ABC
(
                VERSION ASC,
                ID2 ASC,
                TIMESTAMP ASC
);

say there is a high volume of data on the same table. Would you be able to help conclude which Query from below would have better performance and why

First query:

SELECT ID, ID2, VERSION, TIMESTAMP
FROM ABC WHERE ID2=123
  AND VERSION =54321

Second query:

SELECT ID, ID2, VERSION, TIMESTAMP
FROM ABC
WHERE ID2='123'
  AND VERSION ='54321'


texts: What is the best way to insert a XML input to an oracle table in stored procedure?

texts:
I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this:

<rowset>
    <row>
        <name>tom</name>
        <family>mack</family>
        ....
    </row>
    <row>
        <name>marry</name>
        <family>skot</family>
        ....
    </row>
    ...
</rowset>

I have just stock in many options that oracle gave me to handle this. using extractvalue and XMLSequence- using DBMS_XMLDOM- using DBMS_XMLStore or many other options.

I want to know which one is better according to the performance and resources that use. the input XML is usually less than 50KB.


texts: Assign to NEW by key in a Postgres trigger

texts:
In the trigger body, how can I assign a value to NEW by it's field name?
That's what I want to do:

some_key = "some_column";
NEW[some_key] = 5;


texts: How to define custom path to MySQL socket file?

texts:
I have following config

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql-socket/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_buffer_pool_size=64M
innodb_log_file_size=16M

[mysqld_safe]
log-error=/var/log/shared/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

So, there is a custom path to the socket file /var/lib/mysql-socket/mysql.sock.

MySQL server works but I cannot connect to it.

$ mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

but needed file is exists.

$ ls /var/lib/mysql-socket/mysql.sock
/var/lib/mysql-socket/mysql.sock

When path to the socket file is /var/lib/mysql/mysql.sock it works fine.


texts: How to get rid of PGAdmin 'Notifications' panel

texts:
I pushed some key combination by mistake that made this panel pop up:

Now I can't find how to make it disappear. The solution is not evident at all, and it is really bothering because it covers a big part of the results panel. I already tried closing the application and nothing, it is still there when I open it again.
EDIT
Now things are getting worst. Now I dettached the results panel trying to change its size:

Can't find how to attach it again.


texts: postgres ERROR: relation "routine_privileges" does not exist

texts:
I am trying to put together a query to find unused indexes in a postgres database:

SELECT indexrelname, pg_relation_size(quote_ident(indexrelname)), idx_scan
FROM pg_stat_all_indexes
WHERE idx_scan=0
    AND indexrelname NOT LIKE 'pg_toast_%';

The above seems to work all right, but I want to concentrate on the big indexes, so I added an extra condition:

SELECT indexrelname, pg_relation_size(quote_ident(indexrelname)), idx_scan
FROM pg_stat_all_indexes
WHERE idx_scan=0
    AND indexrelname NOT LIKE 'pg_toast_%'
    AND pg_relation_size(quote_ident(indexrelname)) > 1000000 ;

Which resulted in

ERROR:  relation "routine_privileges" does not exist

Could someone tell me why the second query is wrong and how to fix it?


texts: What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables or pgstattuple?


texts: Creating non-clustered index on large table fills transaction logs

texts:
Using SQL Server 2016, I'm trying to create a non-clustered covering index on a table in my database that is of the structure int, bigint, int, varchar(20), varchar(4000). The size is ~13,241,928 rows.

create nonclustered index nix_TableName on Schema.TableName (
    Column2 asc, Column3, asc
) include (Column4, Column5) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The problem is the transaction logs fill up to quickly which sit on a drive partitioned for 50GB. The db is sitting on an availability group with 3 nodes, DW1, DW2, DW3.

What would be the most optimal/practical way of getting this index created without crashing? Can I throttle the index creation, like do it in batches? Are there any tricks I'm missing that I can include in my create nonclustered index statement?

I did find this documentation. I'm trying a few of these tips next.

There is already a clustered column-store index on the table.

Furthermore checking the activity monitor for the server I see that the process for the index creation is being suspended. With the wait_type being CXPACKET. I'm not specifying a MAX_DOP.


texts: Set the Deadlock Priority to Lowest possible value, but my script is still NOT always the victim in deadlock situations

texts:
I have below two queries on Production:

Query # 1: Script that deletes older historical data from core table "Table1" based on "Date" column

There are ~20 tables that point to "Table1" by foreign key with cascade rule, so when you delete from "Table1", data is deleted from ~20 other tables, too
Script deletes data in batches 50 rows at a time with 1 second interval  between batches
Script does NOT cause Exclusive locks to escalate to table-level lock, when batch is only 50 rows; it only places row/page level (X) locks

Query # 2: Production Application that pulls data from "Table1", "Table2", "Table3" and some other tables

This is heavy read query, that places Shared locks on tables. It does not do any insert/update/delete
Problem:
Sometimes, deadlock occurs when Query # 2 (Prod app) reads data, and I simultaneously run Query # 1 (script deleting older data) in SSMS. Query # 2 is always a victim, as I understand it is because it is NOT doing any changes to transaction log, while Query # 1 does
Trying to resolve this, I've added set deadlock_priority -10 to Query # 1, hoping that it will make Query # 1 victim in any possible deadlocks
First it seemed it worked, Query # 1 became victim in deadlock situations, throwing error 1205 when deleting some of the batches.
BUT - recently I discovered that sometimes Query # 2 still becomes a victim
Question:
How is this possible that Query # 2 sometimes becomes a victim even after I've set deadlock_priority -10 for Query # 1 ?
Is there a way to fix it and make sure Query # 1 will be the victim in 100 % cases ?
Update: added graph below


texts: SQL Server: Who created an audit and who altered it last?

texts:
I am trying to find out who created an audit (found under Audits) but I don't seem to have any luck. I've already tried many queries such as these:

Blog 1, Blog 2, Blog 3
And

SELECT *
FROM   [sys].[objects]
SELEcT * FROM sys.server_audits

They seem to work for all other objects, but not for audit. I need to know which user created the audit and which user altered it last. sys.server_audits does not help.


texts: Can I use a rule to insert aggregate data into a new table?

texts:
I can use a RULE for bulk inserts into partitioned tables. For that I used something like

DO INSTEAD
INSERT INTO child_table (col1, col2)
VALUES (NEW.col1, NEW.col2)

From the RULES docs:

  command
  The command or commands that make up the rule action. Valid commands are SELECT, INSERT, UPDATE, DELETE, or NOTIFY.

How do I write something like?

DO ALSO
INSERT INTO agg_table (col1, agg_col)
VALUES (NEW.col1, AVG(NEW.col2))
GROUP BY NEW.col1

Which gives me a syntax error at GROUP


texts: Select max(pk) instead of sequence.nextval

texts:
I have a client/superior who keeps suggesting that I select max(primary_key)
when I insert a new row.

I gut says it's VERY wrong but I don't have exact reasons to support my suggestion.
The only thing so far I can think of is when two people are trying to insert into the same table and getting data mixed up.

What are the reasons one should not rely on selecting max(primary_key) + 1
to generate a pk value ?


texts: Can I log which stored procedure issued a delete statement?

texts:
We have an issue where rows are deleted from a table and we can't figure out where this is happening.  We have a trigger on the table to move the rows to another table, so that we can move the data back to the original table.

There are 20 to 30 stored procedures that delete rows from the table, so I was wondering if there was some way for the delete trigger to know what procedure invoked it.

We're using Sybase 15.

Thanks!