I deleted a folder from /var/lib/mysql and now all of my databases seem to be unaccessible I was receiving this error when trying to drop a database:
ERROR 1010 (HY000): Error dropping database (can't rmdir './redpopdigital@002ecom', errno: 39 "Directory not empty")

So I went into /var/lib/mysql and just did an rm -rf. I did not know that this would screw with literally every other database.
Now it seems all of my databases are inaccessible.
I tried this as a troubleshooting step:
ubuntu@blainelafreniere:~$ mysqlcheck --repair blainelafreniere -u root -p
Enter password:
blainelafreniere.wp_commentmeta
Error    : Table 'blainelafreniere.wp_commentmeta' doesn't exist in engine
status   : Operation failed
blainelafreniere.wp_comments
Error    : Table 'blainelafreniere.wp_comments' doesn't exist in engine
status   : Operation failed
blainelafreniere.wp_links
Error    : Table 'blainelafreniere.wp_links' doesn't exist in engine
status   : Operation failed

what's weird is, all of the data appears to be chilling in /var/lib/mysql... but I can't access it for some reason?
Is there any hope of recovering the data from /var/lib/mysql or am I completely screwed?
Thanks.

mysqlcheck won't help, it's for damaged files.
You should have deleted non-table files in the directory, not the directory itself. Especially if you use MySQL 8, as I suspect. Because your tables metadata are stored in an internal dictionary.
Anyway, don't you have a backup?
If not, recreate the directory and try running individual DROP TABLEs. It may work or not. Let me know how it goes.


Oracle Packages, Procedures and Functions Not Respecting Roles When you create a package, stored procedure or function in one schema (say, SCHEMA1) that accesses objects in another schema (SCHEMA2), even when SCHEMA1 has appropriate permissions to the respective SCHEMA2 objects, granted through roles, Oracle will complain that the SCHEMA2 object cannot be found. Even if you are calling a package as SCHEMA2 that is set to authid current_user, it complains. Apparently, to get around this problem, you have to explicitly grant permissions to SCHEMA1 (e.g., grant select on schema2.some_object to schema1;).

I have come against this issue several times, now: It's always something I forget about because it seems so counter-intuitive! Could anyone offer an explanation as to why roles aren't respected by PL/SQL objects?

When you execute PL/SQL in a package/procedure/trigger, Oracle disables roles (this is not the case for anonymous blocks). As a result, you have to grant permissions directly, rather than through roles.

Jonathan Lewis explains this well here, plus it's covered in the documentation here.

Invoker/definers rights are documented here.


Normalization using DENSE_RANK and LAST_INSERT_ID db-fiddle
I have created a table called orders (orderId, customersId) and a table called orderDetails (orderId, productId). My goal is to denormalize a table called temp which is like
scanId, customersId, productId
1,1,1
1,1,2
1,2,3
2,3,4
2,1,5
2,1,6

I want to insert a new order for every distinct scanId,customersId, and then for every order insert a productId into orderDetails. So given temp you would see 4 orders created, with the 1st and 4th order containing 2 productIds each.
I am used to SQL Server where we can utilize the OUTPUT clause of an INSERT to tie identities back to an initial INSERT. However MySQL doesn't have anything like that. Following advice from https://stackoverflow.com/questions/7333524/how-can-i-insert-many-rows-into-a-mysql-table-and-return-the-new-ids
I came up with the following:
INSERT INTO orders(customersId)
SELECT customersId
FROM temp
GROUP BY scanId, customersId
ORDER BY scanId, customersId;

INSERT INTO orderDetails(orderId, productId)
SELECT
    DENSE_RANK() OVER(
        ORDER BY scanId, customersId
    ) + LAST_INSERT_ID() -1, productId
FROM temp;

The trick being DENSE_RANK() OVER( ORDER BY scanId, customersId ) + LAST_INSERT_ID() -1 constructs the orderId.
Will this always work?
Specifically, will MySQL always assign autoincrement ids to inserts based on the ORDER BY and then utilzing a windowed function on the partition and order always tie back to the inserted id?

You don't need to stand on your head to get the id:
Step 1: Populate Orders in a single statement:
INSERT INTO Orders (scanId, customerId)   -- Leave out; `id` it will be set
    SELECT DISTINCT scanId, customerId FROM temp;

Step 2: Populate the other table:
INSERT INTO OrderDetails (orderId, productId)
    SELECT Orders.id AS orderId,
           temp.productId
        FROM Orders
        JOIN temp USING (scanId, customerId);

It would be helpful to have
temp:  INDEX(scanId, customerId)


MSSQL - convert list of datetimes in a date range I have one system which stores data like:

CREATE TABLE Reservations
    ([UserId] int, [RoomId] int, [TypeId] int, [Date] datetime)
;

Every row in the table means that Room is reserved for User of some Type in given Date

I need to convert data to this system:

CREATE TABLE Reservations
    ([UserId] int, [RoomId] int, [TypeId] int, [StartDate] datetime, [EndDate] datetime)
;

Or to be more precise, I need to just make select statement for selecting data from old system in a new way. StartDate and EndDate must represent continuous date range.

I prepared this sql fiddle. I tried to write select statement but with no success. What am I missing?

;WITH GRP AS
(
    SELECT
    *
    ,ROW_NUMBER() OVER(ORDER BY [Date] ASC)
        -ROW_NUMBER() OVER(PARTITION BY [UserId], [RoomId], [TypeId] ORDER BY [Date] ASC) AS Grp
FROM
    Reservations
)
, MinMax AS
(
    SELECT
        [UserId], [RoomId], [TypeId]
        ,MIN([Date]) AS StartDate
        ,MAX([Date]) AS EndDate
    FROM
        GRP
    GROUP BY
        [UserId], [RoomId], [TypeId]
        ,Grp
)

SELECT
    [UserId], [RoomId], [TypeId]
    ,StartDate
    ,LEAD(StartDate,1,StartDate) OVER(ORDER BY StartDate) AS EndDate
FROM
    MinMax
ORDER BY
    StartDate
    ,EndDate

Your problem is known as an "Islands and Gaps" problem. As far as I can see your solutions is correct (given that I understood what you try to achieve) up until:

,LEAD(StartDate,1,StartDate) OVER(ORDER BY StartDate) AS EndDate

Why are you replacing the EndDate you calculated in MinMax? Try:

SELECT
    [UserId], [RoomId], [TypeId]
    ,StartDate
    ,EndDate
FROM
    MinMax
ORDER BY
    StartDate
   ,EndDate

Regarding how you calculate the group, I assume that a reservation is for the same room, and type during the whole period. You may want to consider something like:

,ROW_NUMBER() OVER (PARTITION BY [RoomId], [TypeId]
                    ORDER BY [Date] ASC)
-ROW_NUMBER() OVER (PARTITION BY [UserId], [RoomId], [TypeId]
                    ORDER BY [Date] ASC) AS Grp

In your query, GRP changes regardless of which room or type that interferes with the enumeration of dates.

PS. Welcome to the forum. A Very good first question. If GRP puzzles you, I suggest that you look at the row_number functions individually, before looking at their difference DS.

EDIT:

The idea you used uses the fact that some other user interrupts the current ordering, and thereby creates a new grp. Since several users can rent the room at the same time, I don't think that will work. Here is one idea (date is a reserved word so I changed it to dt to be able to get rid of the annoying quotes []):

with start_period as (
    select a.userid, a.roomid, a.typeid, a.dt as start_date
    from Reservations a
    where not exists (
        select 1 from Reservations b
        where a.userid = b.userid
          and a.roomid = b.roomid
          and a.typeid = b.typeid
          and a.dt = dateadd(day, 1, b.dt)
    )
), end_period as (
    select a.userid, a.roomid, a.typeid, a.dt as end_date
    from Reservations a
    where not exists (
        select 1 from Reservations b
        where a.userid = b.userid
          and a.roomid = b.roomid
          and a.typeid = b.typeid
          and a.dt = dateadd(day, -1, b.dt)
    )
)
select x.userid, x.roomid, x.typeid, x.start_date
     , min(y.end_date) as end_date
from start_period x
join end_period y
    on x.userid = y.userid
    and x.roomid = y.roomid
    and x.typeid = y.typeid
    and x.start_date <= y.end_date
group by x.userid, x.roomid, x.typeid, x.start_date;

1   1   2   2020-04-01T00:00:00Z    2020-04-03T00:00:00Z
1   1   2   2020-04-05T00:00:00Z    2020-04-07T00:00:00Z
1   1   2   2020-04-10T00:00:00Z    2020-04-10T00:00:00Z
2   1   2   2020-04-01T00:00:00Z    2020-04-03T00:00:00Z
2   1   2   2020-04-05T00:00:00Z    2020-04-05T00:00:00Z
2   1   3   2020-04-06T00:00:00Z    2020-04-07T00:00:00Z

Fiddle

The idea is pretty simple, get all dates (for a given user, room, type) where there is no predecessor. Then get all dates (for a given user, room, type) where there is no successor. For all start_dates, find the smallest end_date that is bigger than the start_date


SUM counts from parent and child With this SQL-query, I am able to obtain the count(*) of items in subcategory, but I want to be able to SUM total items, from both main (parent) category and subcategory (child), how can this be achieved?

SELECT parent.name,
COUNT(sub.item_id) AS total
FROM categories parent
LEFT JOIN subcategories child ON parent.category_id = child.category_id
LEFT JOIN item_subcategory sub ON child.subcategory_id = sub.subcategory_id
LEFT JOIN items i ON sub.item_id = i.item_id
GROUP BY parent.name;

EDIT: http://sqlfiddle.com/#!9/7ec5b8/1

Note, I am using the latest MySQL on my PC, but the one supported there is 5.6 at highest, for some reason I keep getting syntax error.

EDIT2: This QUERY currently COUNTs ITEMS in subcategories, is IT possible to within the same query count for items in main categories and then SUM these two counts.

SELECT name, COUNT(item_id)
FROM ( SELECT c.name, i.item_id
       FROM categories c
       LEFT JOIN item_category ic ON ic.category_id = c.category_id
       LEFT JOIN items i ON ic.item_id = i.item_id

     UNION ALL

       SELECT c.name, i.item_id
       FROM categories c
       LEFT JOIN subcategories sc ON sc.category_id = c.category_id
       LEFT JOIN item_subcategory isc ON isc.subcategory_id = sc.subcategory_id
       LEFT JOIN items i ON isc.item_id = i.item_id
     ) total
GROUP BY name /* WITH ROLLUP */
;

or

SELECT name, SUM(cnt)
FROM ( SELECT c.name, COUNT(i.item_id) cnt
       FROM categories c
       LEFT JOIN item_category ic ON ic.category_id = c.category_id
       LEFT JOIN items i ON ic.item_id = i.item_id
       GROUP BY c.name

     UNION ALL

       SELECT c.name, COUNT(i.item_id)
       FROM categories c
       LEFT JOIN subcategories sc ON sc.category_id = c.category_id
       LEFT JOIN item_subcategory isc ON isc.subcategory_id = sc.subcategory_id
       LEFT JOIN items i ON isc.item_id = i.item_id
       GROUP BY c.name
     ) total
GROUP BY name /* WITH ROLLUP */
;


Why Write IOPS,Write throughput,Queue depth decreased on AWS/RDS after changing innodb_flush_log_at_trx_commit and sync_binlog? I changed values for my Mysql parameters

1. innodb_flush_log_at_trx_commit=2(before it was 1)

2. sync_binlog=0(before it was 1)

This has changed some of my parameters on AW RDS drastically ,here are some of them.

  Write Operations(Count/sec)

  Queue Depth

My question is ,What can i interpret from the above results ? Is is good for my database ? Is the performance increased ?

Both of those changes improved throughput, but decreased robustness.

innodb_flush_log_at_trx_commit=1 says to flush stuff to disk at every COMMIT, thereby making your database fully crash-proof.  =2 risks one second's worth of COMMITs.

If you have the binlog turned on, sync_binlog=1 says to flush to disk at every COMMIT.  =0 allows delaying (buffering) the writes.  This variable is somewhat important if you have Slaves and/or do incremental backups.


How to enforce a nullable foreign key? I have a relation between two tables.  The foreign key table can have a row related in the primary table.  There's a way to enforce the value of the FK column to be NULL or one of the values of the PK column in the primary table?

Yes, why not? Did you try it?

CREATE TABLE dbo.foo(fooid INT PRIMARY KEY);

CREATE TABLE dbo.bar(barid INT PRIMARY KEY,
  fooid INT NULL FOREIGN KEY REFERENCES dbo.foo(fooid));

INSERT dbo.foo SELECT 1;
INSERT dbo.bar SELECT 1,1;
INSERT dbo.bar SELECT 2,NULL; -- succeeds
GO
INSERT dbo.bar SELECT 3,2; -- fails

If this is not what you meant, then please elaborate or re-word your question.


MySQL Replication: Seconds Behind Master super high I've set up a slave db server for my production database, but when I checked the show slave status, I noticed a super big number in seconds behind master.

This is the output:

           Slave_IO_State: Waiting for master to send event
              Master_Host: 1.2.3.4
              Master_User: replicator
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000173
      Read_Master_Log_Pos: 15909435
           Relay_Log_File: mysqld-relay-bin.000079
            Relay_Log_Pos: 91173356
    Relay_Master_Log_File: mysql-bin.000093
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 91173210
          Relay_Log_Space: 8179978166
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: 486330
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
           Last_SQL_Error:
Replicate_Ignore_Server_Ids:
         Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR:
No query specified

Then when I run SHOW PROCESSLIST, I see that the time of the thread matches the time indicated in seconds behind:

mysql> SHOW PROCESSLIST;

| 40 | system user |           | NULL | Connect |  66530 | Waiting for master to send event | NULL             |
| 41 | system user |           | NULL | Connect | 486330 | Reading event from the relay log | NULL             |
| 45 | root        | localhost | NULL | Query   |      0 | NULL                             | SHOW PROCESSLIST |

That time is dropping, slowly. Read_Master_Log_Pos,  Relay_Log_Pos, Exec_Master_Log_Pos and Relay_Log_Space are changing all the time.

I've also check the time/date and both servers are on sync.

On the Master side:

mysql> SHOW PROCESSLIST;

| 66739 | replicator | 1.2.3.5:52884 | NULL                | Binlog Dump |    65671 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL

and show slave hosts looks empty...

mysql> SHOW SLAVE HOSTS;
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
|         2 |      | 3306 |         1 |
+-----------+------+------+-----------+
1 row in set (0.00 sec)

mysql>

So what is actually going on here? Looks like the slave is actually connected and working, but very very slow? Can someone give me some hints on how to do more debug on this? Server is rather idle at 95%.

When you see the Seconds_Behind_Master that high, I look at the following:

Relay_Log_Space: 8179978166

You have  7.6182GB of relay logs to process.

Master_Log_File: mysql-bin.000173
Relay_Master_Log_File: mysql-bin.000093

This tell me that you have read up to mysql-bin.000173, but you are currently processing things from the mysql-bin.000093.

This also tell me you have about 80 binary logs on the Master, each about 100 MB.

The Seconds_Behind_Master is simply the NOW() minus the TIMESTAMP set at mysql-bin.000093 (Relay_Master_Log_File) position 91173210 (Exec_Master_Log_Pos).

As long as Slave_SQL_Thread is Yes, the relay logs get processed

Relay_Log_Space will decrease each time a relay log is done
Exec_Master_Log_Pos will increase until current relay log is done, then resets to the beginning of the next relay
TIMESTAMP keeps increasing, which make Seconds_Behind_Master decrease (NOW() minus the TIMESTAMP set at Relay_Master_Log_File position Exec_Master_Log_Pos)

This is what happens when replication is off for 486330 seconds (5 days 15 hours 5 minutes 29 seconds) and you run start slave;

Look at your SHOW PROCESSLIST;. The IO Thread has been up for 66530 seconds (18 hours 28 minutes 50 seconds). This means someone or something started replication 18 hours 28 minutes 50 seconds ago.

You stated in your question that you have set up replication for the production server. This means that you ran the mysqldump 5 days 15 hours 5 minutes 29 seconds ago and started replicating from the production master 18 hours 28 minutes 50 seconds ago.

If you had setup the Slave the same day you got the mysqldump from the Master the replication load would be a lot less. Notwithstanding, replication is working normally provided Slave_IO_Thread and Slave_SQL_Thread both say Yes.


PostgreSQL function parameters JSON to TYPE I try to connect my NodeJS API to my database.

I have created some type of object for the transport.
I use LANGUAGE plpgsql.

One example :

API_CAR: this type exists on my database

I receive an object API_CAR in JSON

{
"car_id": 18,
"car_brand": "AUDI",
"car_model": "A4",
"car_nb_places": 5,
"car_picture_url": "http://...",
"horse_power": 3
}

And I would like to pass the data directly to my function :

SELECT * FROM carPATCH( ... );

I know that I can CAST it like this:

SELECT * FROM carPATCH
(
   (18,'et','vel',5,'Donec dignsque',3):: API_CAR
);

But it's complicated to do this trick.

My question is what you would advise:

Is it better to do this transformation outside the database in NodeJS or can I pass this JSON directly inside and transform it after?

I think it should be possible to transform a JSON to another type but I don't know how to do it.

This is a simple example, but I have some big structure to forward to the database.

One can use json_populate_record() to achieve this.  It's very easy to use:

json_populate_record(null::API_CAR, <your JSON here>)

Exemple with NodeJS :

exports.carPATCH = function (apiKey, args, res, next) {

    let car = "'" + JSON.stringify(args.v_car.value) + "'";
    apiKey = "'" + apiKey + "'";

    DB.query.any("SELECT * FROM v01_api_patch_carPATCH" +
        "(json_populate_record(NULL::API_CAR," + car + ")," + apiKey + ")", [true])
        .then(data => {
            res = resp.response_201(res, data);
        })
        .catch(error => {
            res = resp.error(res, error);
        })
};

And after, my function work perfectly because it's an object API_CAR


Error in IF statement in MySQL I am wondering why I am getting error when running below simple IF in MySQL?

IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE employee_number = 10 AND attendance_date_time = '2015-10-11 13:14:16') THEN
BEGIN
    INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time)  VALUES (10, '2015-10-11 13:14:16');
END;
END IF;

This is the error I am getting:

  [SQL] IF NOT EXISTS (SELECT employee_attendance_id FROM
  employee_attendance WHERE employee_number = 10 AND
  attendance_date_time = '2015-10-11 13:14:16') THEN BEGIN
      INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time)  VALUES (10, '2015-10-11 13:14:16'); [Err] 1064
  - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
  near 'IF NOT EXISTS (SELECT employee_attendance_id FROM
  employee_attendance WHERE empl' at line 1

Thanks,
Jassim

You cannot execute such statements unless you use a stored proc:
You can run this below. It should work.

DROP procedure IF EXISTS `test2`;
DELIMITER $$
CREATE  PROCEDURE `test2`(p_employee_number int, p_date datetime)
Begin

if  NOT EXISTS (SELECT 1
    FROM employee_attendance
        WHERE employee_number =p_employee_number  AND attendance_date_time = p_date) THEN

    INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time)  VALUES (p_employee_number, p_date);

End if ;
END$$

DELIMITER ;


Optimizing queries on a range of timestamps (one column) I am using Postgres 9.3 through Heroku.

I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ranges and those calls can take up to 40 seconds and would love to hear suggestions on how to improve that.

I have the following index in place on this table:

CREATE INDEX idx_traffic_partner_only ON traffic (dt_created) WHERE campaign_id IS NULL AND uuid_self <> uuid_partner;

Here is an example SELECT statement:

SELECT SUM("clicks") AS clicks, SUM("impressions") AS impressions
FROM "traffic"
WHERE "uuid_self" != "uuid_partner"
AND "campaign_id" is NULL
AND "dt_created" >= 'Sun, 29 Mar 2015 00:00:00 +0000'
AND "dt_created" <= 'Mon, 27 Apr 2015 23:59:59 +0000'

And this is the EXPLAIN ANALYZE:

Aggregate  (cost=21625.91..21625.92 rows=1 width=16) (actual time=41804.754..41804.754 rows=1 loops=1)
  ->  Index Scan using idx_traffic_partner_only on traffic  (cost=0.09..20085.11 rows=308159 width=16) (actual time=1.409..41617.976 rows=302392 loops=1)
      Index Cond: ((dt_created >= '2015-03-29'::date) AND (dt_created <= '2015-04-27'::date))
Total runtime: 41804.893 ms

http://explain.depesz.com/s/gGA

This question is very similar to another on SE, but that one used an index across two column timestamp ranges and the index planner for that query had estimates that were way off. The main suggestion there was to create a sorted multi-column index, but for single column indexes that doesn't have much of an effect. The other suggestions were to use CLUSTER / pg_repack and GIST indexes, but I haven't tried them yet, since I'd like to see if there is a better solution using regular indexes.

Optimizing queries on a range of timestamps (two columns)

For reference, I tried the following indexes, which were not used by the DB:

INDEX idx_traffic_2 ON traffic (campaign_id, uuid_self, uuid_partner, dt_created);
INDEX idx_traffic_3 ON traffic (dt_created);
INDEX idx_traffic_4 ON traffic (uuid_self);
INDEX idx_traffic_5 ON traffic (uuid_partner);

EDIT: Ran EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS) and these were results:

Aggregate  (cost=20538.62..20538.62 rows=1 width=8) (actual time=526.778..526.778 rows=1 loops=1)
  Output: sum(clicks), sum(impressions)
  Buffers: shared hit=47783 read=29803 dirtied=4
  I/O Timings: read=184.936
  ->  Index Scan using idx_traffic_partner_only on public.traffic  (cost=0.09..20224.74 rows=313881 width=8) (actual time=0.049..431.501 rows=302405 loops=1)
      Output: id, uuid_self, uuid_partner, impressions, clicks, dt_created... (other fields redacted)
      Index Cond: ((traffic.dt_created >= '2015-03-29'::date) AND (traffic.dt_created <= '2015-04-27'::date))
      Buffers: shared hit=47783 read=29803 dirtied=4
      I/O Timings: read=184.936
Total runtime: 526.881 ms

http://explain.depesz.com/s/7Gu6

Table definition:

CREATE TABLE traffic (
    id              serial,
    uuid_self       uuid not null,
    uuid_partner    uuid not null,
    impressions     integer NOT NULL DEFAULT 1,
    clicks          integer NOT NULL DEFAULT 0,
    campaign_id     integer,
    dt_created      DATE DEFAULT CURRENT_DATE NOT NULL,
    dt_updated      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
)

id is the primary key and uuid_self, uuid_partner, and campaign_id are all foreign keys. The dt_updated field is updated with a postgres function.

Two things that are very odd here:

The query select 300k rows from a table with 1M+ rows. For 30 % (or anything over 5 % - depends on row size and other factors) it doesn't typically pay to use an index at all. We should see a sequential scan.

The exception would be index-only scans, which I don't see here. The multicolumn index @Craig suggested would be the best option if you get index-only scans out of it. With lots of updates like you mentioned, this may not work out, in which case you are better off without the additional columns - and just the index you already have. You might be able to make it work for you with more aggressive autovacuum settings for the table. You can adjust parameters for individual tables.
While Postgres is going to use the index I would certainly expect to see a bitmap index scan for that many rows, not a plain index scan, which is typically the better choice for a low percentage of rows. As soon as Postgres expects multiple hits per data page (judging from its statistics on the table) it will typically switch to a bitmap index scan.

Keep PostgreSQL from sometimes choosing a bad query plan

Judging from that I would suspect that your cost settings are inadequate (and possibly the table statistics, too). You  may have set random_page_cost and / or cpu_index_tuple_cost too low, relative to seq_page_cost. Follow the links and read the manual.

Would also fit with the observation that cold cache is large factor, as we worked out in comments. Either you are accessing (parts of) tables that nobody has touched in a long time or you are running on a test system where the cache isn't populated (yet)?
Else you just don't have enough RAM available to cache most of the relevant data in your DB. Consequently, random access is much more expensive than sequential access when data resides in cache. Depending on the actual situation you may have to adjust to get better query plans.

One other factor has to be mentioned for slow response on the first read only: Hint bits. Read details in the Postgres Wiki and this related question:

Why does a SELECT statement dirty cache buffers in Postgres?

Or the table is extremely bloated, in which case an index scan would make sense and I would refer back to CLUSTER / pg_repack in my previous answer that you quoted. (Or just VACUUM FULL) And investigate your VACUUM settings. Those are important with many inserts and updates every day.

Depending on UPDATE patterns also consider a FILLFACTOR below 100. If you mostly update only newly added rows, set the lower FILLFACTER after compacting your table, so that only new pages keep some wiggle room for updates.

What fillfactor for caching table?
Are regular VACUUM ANALYZE still recommended under 9.1?

Schema

  campaign_id is 99%+ NULL and dt_updated is 0% NULL.

Adjust the sequence of columns slightly, to save 8 bytes per row (in the 99% of cases where campaign_id is NULL):

CREATE TABLE traffic (
    uuid_self       uuid not null REFERENCES ... ,
    uuid_partner    uuid not null REFERENCES ... ,
    id              serial PRIMARY KEY,
    impressions     integer NOT NULL DEFAULT 1,
    clicks          integer NOT NULL DEFAULT 0,
    campaign_id     integer,
    dt_created      DATE DEFAULT CURRENT_DATE NOT NULL,
    dt_updated      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
);

Detailed explanation and links to more:

Configuring PostgreSQL for read performance

To measure:

Measure the size of a PostgreSQL table row


Excel grab data from MS SQL Server with WHERE clause based on cell contents There's a bunch of questions for similar things, but I've been unable to find anything that fits.

We have three columns:

Name   |    ID    |    Cost
Thingy       3          25
Other        11         5

Only ID is manually entered. Name and cost on that row are then fetched automatically from an MSSQL database.

I've been looking at using Microsoft Query data source with ? in the WHERE clause to then add parameters based on cell contents but firstly, the parameters button is greyed out and secondly, even if it worked, it seems like I'd need to manually create queries for every single row with cell reference hardcoded in rather than one cell query that could be replicated down the name and cost columns.

Any guidance would be hugely appreciated.

Could you make a tab in your sheet that holds all of your SQL data?  Each time you load your sheet you could refresh this data to make it fresh then everything else could be an Excel formula (lookup on SQL tab) to get at what you want.  My reason for suggesting this is you sound a lot more familiar with Excel over MS Query.

Otherwise in MS Query use your table as a data source and join with the SQL data (again I'd probably throw the result in a new tab)


How to restrict SSMS to list the database of a particular user (Windows 2012 + Plesk) We are using windows 2012 R2 server with Plesk and provide shared hosting for our customers.

Version Details: MSSQL server 2012 and SSMS 11.0.2100.60

In SSMS if we login with particular user, it is displaying all the database instead of particular user's database. Displaying all DBs to all users, it is a security issue. (though, they cant access the DB, Just displays the name).

How can I restrict it, to display only the DB which belongs to that specific user.

Using at least SQL Server Management Studio -16.3, you can right click on the Databases node in SSMS and add a filter to restrict displaying databases owned by that user.


Need explanation SELECT query on many to many relationship I have two tables related through another (many-to-many)

This is a schema excerpt:

CREATE TABLE user (
    user_id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    email    TEXT    NOT NULL UNIQUE
);

CREATE TABLE alias (
    alias_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    address  TEXT    NOT NULL UNIQUE
);

CREATE TABLE alias_member (
    alias_id INTEGER NOT NULL,
    user_id  INTEGER NOT NULL,
    PRIMARY KEY(alias_id, user_id),
    FOREIGN KEY(alias_id) REFERENCES alias(alias_id) ON DELETE CASCADE,
    FOREIGN KEY(user_id)  REFERENCES user(user_id)   ON DELETE CASCADE
);

I have this query that satisfies my needs:

SELECT email
FROM
    user,
    alias,
    alias_member
WHERE
    user.user_id = alias_member.user_id
    AND alias.alias_id = alias_member.alias_id
    AND alias.address = 'foo@domain.tld';

A friend suggested me another query that works too but I don't get it:

SELECT
    email
FROM
    user
INNER JOIN
    (alias INNER JOIN alias_member ON alias.alias_id = alias_member.alias_id) ON user.user_id = alias_member.user_id
WHERE address='foo@domain.tld';

Would anyone explain it for me?

Your version uses a very old JOIN syntax from the ANSI-89 standards - as in 1989. In 1992, the standard was updated to adopt the JOIN...ON syntax. You can read about the differences and their adoption in this SO question.

The problem with listing tables in a FROM and then listing the conditions in the WHERE is that it's very easy to forget a condition somewhere. How easy would it be to accidentally write:

SELECT email
FROM
    user,
    alias,
    alias_member
WHERE
    user.user_id = alias_member.user_id
    AND alias.address = 'foo@domain.tld';

Now, your friend's version also uses a weird convention (the "nested" JOIN). Here, it's trying to help with the fact that you have alias_member as the last table in the JOIN, but is needed to connect the other 2 tables. If alias_member is the first table, the syntax gets a bit cleaner:

SELECT
    email
FROM
    alias_member
INNER JOIN
    user
  ON alias_member.user_id = user.user_id
INNER JOIN
    alias
  ON alias_member.alias_id = alias.alias_id
WHERE address='foo@domain.tld';


Heavy I/O for Microsoft Transaction Log We have a Microsoft SQL Server (SQL Server 2012 SP3), we've noticed that transaction log I/O is very high. We do have a busy server; we're hosted at Rackspace with SSD block storage.  The Logs are are their own partition, and the log is set to "auto grow" and is about 70GB in size (data file is about 100GB)

Any thoughts on how to improve I/O performance for the transaction logs?  We have a sense that it's affecting performance.

Transaction Logs are backedup every 30 minutes
I/O on the transaction log partition is 47% during normal activity
and when we run one of our jobs it goes to 90%+
We have a 8 vCPU cloud server and we have 8 temp db and 8 temp log
Attached performance graphsdb performance graph

I should mention that we don't have a DBA, I do have some SQL experience and we have SQL developers.

DBCC Log (info) shows 456 rows

NEW QUESTION: Could it be that my transaction log at 60GB is TOO LARGE? Would I get less I/O making the trans log smaller, say 20GB-30GB?

Thing i would do to make the transactionlog faster.

Check NTFS block size, and reformat drive to 64k
Check VLF's (should warn in EXEC xp_readerrorlog) if you above 1k.. shrink/grow file to fix.
Set sensible log-file growth rates (not 1% or 1MB)
Consider using Delayed Durability where its possible (be mindfull of Data integrity/DR)

The all-knowing K. Tripp has an article on this
http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/


Unsure how to implement certain constraints and relations in this relational database schema I am in the middle of designing the dB for school project- airline ticketing database.(Using MySQL workbench)

So far, I came up with the following design:

Here are couple things I can't figure out:

Is it good idea to have Foreign keys as non-int values (such as Varchar) ??
Database needs to somehow keep track of Number of seats booked & Number of passengers on board for particular flight. Have no idea where to place those attributes.
How to make sure that Arrival City and Departure City (from Flight table) are different for particular flight ?

You get what you pay for on SO.  The advice against using natural keys (names instead of numbers, in this case) is common, but misguided.  If you don't believe me, ask your professor.

The number of seats would seem to be a property of the plane, right?  The number of booked seats would seem to be the count of seats booked, grouped by flight number.

The arrival and departure cities can be constrained with a CHECK constraint.  You might also want to ensure that departures happen before arrivals (assuming 21st century airport).

Your design is pretty good, better than some of the advice you're getting.  Stick with it, don't worry about datatypes for your keys, don't look for fanciness like triggers to implement application logic (and don't fob off real constraints to the application).  Do RTFM for CREATE TABLE several times.  If you think of a constraint that can't be declared, I suggest you document it in your project as a flaw in the DBMS.

Do think very carefully about what's unique.  I doubt you need BookingID, because PassengerID, Flight#, and Seat would seem to be unique.  Passengers are complex (in a real database you'd have a bunch of tables to describe someone) so they need an ID for convenient reference, but still you likely wouldn't want two records sharing name & address.


Script to monitor database growth on daily basis I am fairly new to databases, I have got a specific requirement to script a Job/Stored Procedure to monitor the database growth.

We are currently using MS SQL Server 2016 Standard Edition with Always-ON HA.

I have setup mail server on the database.
It would be even more efficient to link the output of the job to the mail.

There is a stored procedure called SpaceUsedRating:

https://github.com/aleksey-vitsko/Database-Administrator-Tools

When you execute it exec SpaceUsedRating without any parameters, it will show space usage stats inside a given database

This procedure can also be called with parameter @command = 'log'

When called with @command = 'log' parameter, this SP will log result to logging table DatabaseGrowthLogger at database ServerLogsDB (DB and table need to be created)

Here is the script for creating logging table

USE [ServerLogsDB]
GO

/****** Object:  Table [dbo].[DatabaseGrowthLogger]    Script Date: 5/12/2020 12:11:14 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DatabaseGrowthLogger](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Server_Name] [varchar](50) NULL,
    [Database_Name] [varchar](250) NOT NULL,
    [Date_Full] [smalldatetime] NOT NULL,
    [Day_Of_Week] [tinyint] NULL,
    [Log_Date] [date] NULL,
    [Log_Hour] [tinyint] NULL,
    [Table_Count] [int] NULL,
    [Total_Rows] [bigint] NULL,
    [Database_Data_MB] [decimal](16, 2) NULL,
    [Database_Index_MB] [decimal](16, 2) NULL,
    [Database_Unused_MB] [decimal](16, 2) NULL,
    [Database_Allocated_MB] [int] NULL,
    [Database_File_Size_MB] [int] NULL,
    [Percent_Used] [decimal](5, 2) NULL,
    [Hours_Diff] [smallint] NULL,
    [Database_Allocated_Delta] [int] NULL,
    [Percent_Used_Delta] [decimal](5, 2) NULL,
PRIMARY KEY CLUSTERED
(
    [Database_Name] ASC,
    [Date_Full] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DatabaseGrowthLogger] ADD  DEFAULT ((0)) FOR [Hours_Diff]
GO

ALTER TABLE [dbo].[DatabaseGrowthLogger] ADD  DEFAULT ((0)) FOR [Database_Allocated_Delta]
GO

ALTER TABLE [dbo].[DatabaseGrowthLogger] ADD  DEFAULT ((0)) FOR [Percent_Used_Delta]
GO

After you create ServerLogsDB..DatabaseGrowthLogger database and table, and deploy stored procedure SpaceUsedRating to all databases you want to monitor growth for, you need to schedule a job that will run once a day, for example at 23:58 UTC, and run below t-sql:

exec Database1..SpaceUsedRating @command = 'log'
exec Database2..SpaceUsedRating @command = 'log'
...
-- create SP in whatever number of databases you need to monitor growth

This will log daily growth of your databases, and when you check ServerLogsDB..DatabaseGrowthLogger table, it will look similar to below:

Looking at logging table, you will have an idea how much data inside your database has grown over time

Finally, you can add a step to the job, that will read last row from ServerLogsDB..DatabaseGrowthLogger and send it by email using sp_send_dbmail procedure


How to deal with a large database containing zipped files. (I'm a PhD student in applied math, I know some programming but admittedly I know very little about databases.)

My question is: how do I tackle/handle this database?

The problem is as follows. I have a "large" database (1 Tb). The top level maps are 15 batches which are named BATCH1 ... BATCH15 as follows:

The only relevant map is the RawTAS map (actually the TAS map, but RawTAS will do for the sake of this question)

Going one level deeper we find the following directory structure

Each RawTAS map has a year folder going from 2006 to 2015 and then each year folder has monthly folders going from 01 to 12. Each monthly folder now contains a bunch of zipped csv files. The issue is that when I exctract these zip files they become insanely large so unzipping all of them is infeasible.

What would be the proper way of handling this kind of database?

Your file system is already a database, one made specifically for storing files in a hierarchical structure just like the one you have here. So without knowing more about your use case, the proper way of handling them is exactly what you have already.

If the problem is that extracting all the zip files uses too much disk space, the obvious solution is to not extract them until you actually need them.


Running a task without waiting for the results in Postgres 11 on RDS I've got a question about spawning stand-alone threads on Postgres. Here's what I'd ideally like:

Have a postgres-side solution to automatically launch tasks on a schedule in their own threads.

Something like the (imaginary) example below:

   CALL_IN_NEW_THREAD do_db_maintenance();

I understand that this doesn't exist, but I don't know what is possible or preferable. I'm in design phase, so my question isn't about SQL syntax, etc. I'm trying to figure out if there is a way to "detach" a query from the original process so that my connection can close down without waiting for the task to finish. Or, even better, if there is a way to auto-start routine tasks on Postgres itself without a client kicking it off or responding to a NOTIFY.

First up, the setup:

We've got a number of routine tasks to run periodically. Imagine refreshing materialized views, integrating posted changes, DB admin chores, etc.
The results of a query/task are written back to the database, so there's no need for the client to collect them.
Imagine that we've got a task_definition table that makes it simple to figure out which tasks are pending and should be run.
Our deployment environment is Postgres 11.x on RDS, so pg_cron is not available. In fact, the command-line isn't available.
We'll be on Postgres 11.x until RDS certifies PG 12. So, I'm looking for a PG-11 based solution for the foreseeable future, likely the better part of the next year.

What I am hoping to do goes like this:

Have an external program log into Postgres, scan the task_definition table, find the jobs that should be started, and start them.
"Start them" in distinct threads.
And then log out.

So, the client just kicks off the work and gets out. Is this a possibility in Postgres without a shared library or anything else that's impossible on RDS?

If it's not possible, I can always have my external program log in, find pending tasks, and launch them in distinct threads from the client. (Be kind and assume that I've handled the locking correctly and am not double-launching any of the tasks.) It seems a bit silly to have the client connection hanging around for no real purpose, but it's not the end of the world. On the upside, it might make it easier to notice if I screw something up and have a task that takes too long.

I've looked around at various solutions for scheduled tasks in Postgres, it seems to be a common requirement. A few notes on solutions I've seen:

Triggers
Sure, that makes sense...they work as a kind of signal. But then you've tied up the process that's running the trigger until the task is done. (There doesn't seem to be a way to detach a procedure call.) So, this doesn't feel like a great help. I guess I could have a utility table where the trigger serves no other purpose than to run a method, but I don't see what that offers over just running the method in a query directly.

pg_cron
Not available on RDS.

PgAgent
Not available on RDS.

cron
Not available on RDS. But you could set up an EC2 instance, etc. to run cron.

Your language here
Use your language of choice to connect, run, wait, repeat. I'm guessing that this is what I'll end up having to do.

NOTIFY/LISTEN
Cool! But it still requires "Your language here" to do the listening, so I don't think it changes anything for me.

dblink
This sounds promising, but I'm not sure it's what I'm after. dblink_send_query supports one async query per connection, and it sounds like the connection has to be sustained. It also doesn't appear from the docs that you can dblink back to the same database. We don't have a second DB running now, but will at some point...so dblink is something we would consider.

You seem to be beating your head against the limitations of RDS.  Is what RDS offers you worth the limitations, over just renting the hardware and running your own database on it?

Triggers and NOTIFY/LISTEN are for event-driven things, not clock-driven things.  They are for solving a different problem than the one you have.

My prefered approach is to combine 'cron' and 'Your language here'.  Write the job in your language, launch it with cron.  For example if I am writing a web-app, I actually write the maintenance logic into the web-app, and then have cron use wget to hit a special URI to invoke it.  Yes, that would require having a non-RDS machine that lets you run cron and your language of choice on it (unless you give up on RDS), but if you have a web-app, you already have an app-server which could be used for that (unless that machine is also AWS-managed with no CLI access).

You could also use pgAgent in the same way.  You would need a separate machine to run the pgAgent software.  You could also run a tiny database on that machine to hold the jobs--pgAgent can read the jobs from one database but execute them against another one.  I think (but I have not tested this) that you can even put the jobs into a schema of your main database.  While you cannot use 'CREATE EXTENSION pgagent', you can run the script manually to create all of the objects.  (And if you do want to use pgAgent, you could bug AWS to whitelist it--since it does not require a superuser there is no reason not to do so other than the effort involved)


Save data to variable and use it in stored procedure I have a table of 2 columns (Date, Text). I am getting data from the Text column according to the Date using a query like this:

SELECT Text
FROM MyTable
WHERE Date>= @userDate;

This works, except when @userDate is bigger than the most recent Date in MyTable. In this case it returns nothing but I need it to return the Text for the most recent Date.

I have to change my query to use the smaller one of @userDate and MAX(Date). Any ideas how to do it?

SELECT CASE WHEN T.Date IS NULL THEN M.TEXT ELSE T.TEXT END TEXT
     FROM (SELECT TOP(1) *
              FROM MyTable
          ORDER BY Date DESC) M
LEFT JOIN MyTable T ON T.Date>= @userDate11

This is one of several ways to solve the problem.  The TOP(1) and ORDER BY Date DESC gives you the fallback record.  The original table aliased T is consigned as the optional partner in the LEFT JOIN.  This allows it to return no records, while still retaining the singular record from the derived table aliased M. The CASE statement then tests to see if we actually managed to get anything from T using WHEN T.Date IS NULL - if T is not empty, all the TEXT values will come from T, otherwise we will have just the single TEXT value from M, being the one for the MAX(Date).


Appending data to the existing database I've got a quick question , that would be great if anyone can help me with this problem:
I have 2 SQL 2012 databases A & B , they are both Identical same tables and same structures as well.
Data is inserted into DB A and on regular schedule I need the data to be inserted into DB B automatically and APPENDS to the existing data in DB B,after that, data in DB A gonna deleted and the whole process starts over again with new records.I couldn't preform this action using any methods of replication because once the data is deleted from DB A same thing gonna happen in DB B.
On the other hand these data contains FK pointing to other tables in the database so if the first problem solves and it starts APPENDING data to the existing data on DB B all these FK should be updated with new values in DB B.
Do you guys have any solution using SQL itself or any other tools?

You can use Transactional Replication, but you have to exclude the DELETEs. Just replicate INSERTs and UPDATEs.

Here's a related article.


How to design a table/ unique index where uniqueness is temporal? I have a table that records observed movies in a cinema:

CREATE TABLE cinema_movie_name (
  id integer DEFAULT nextval('cinema_movie_name_id_seq'::regclass) PRIMARY KEY,
  cinema_id integer NOT NULL REFERENCES cinema(id) ON DELETE CASCADE ON UPDATE RESTRICT,
  name text NOT NULL,
  movie_id integer REFERENCES movie(id) ON DELETE SET NULL ON UPDATE RESTRICT,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

Every time we see a new movie name in the cinema, we record it in this table and map it to movie_id in our database.

The current uniqueness is enforced on (cinema_id, name), i.e.

CREATE UNIQUE INDEX cinema_movie_name_cinema_id_name_idx ON cinema_movie_name(cinema_id, name);

The problem is that over a long time, there can be multiple different movies with the same name, i.e. after some arbitrary time duration (id, cinema_id, name, movie_id) becomes invalid.

Therefore, I need to create an index that enforces a unique constraint on (cinema_id, name) and created_at ±3 month.

How to create a unique index with a temporal column?

I would add a column is_current or something similar that indicates if the movie is a "current" one, then you can create a unique index on only the "current ones"

CREATE UNIQUE INDEX cinema_movie_name_cinema_id_name_idx
    ON cinema_movie_name(cinema_id, name)
where is_current;

You can have a regular job that sets old movies to is_current = false


SQL Server Extended Events purging How do we purge events older than a day in Extended Events in SQL Server 2012. Stopping and Starting the session is not helping.
Target : asynchronous_file_target
Trying to create a job which will perform this operation daily.

The example PowerShell command below will delete trace file target rollover files that haven't been written to for more than 24 hours. Change the trace file pattern to match your actual trace file target and schedule in a SQL Agent job PowerShell step for the daily purge.

Get-ChildItem  "C:\SqlTraceFiles\YourTraceName*.xel" | Where-Object -Property LastWriteTime -lt (Get-Date).AddDays(-1) | Remove-Item -ErrorAction Ignore

This command will not delete the active trace file since it's in use (ignoring the error). If the event volume is too low to cause a rollover at least once per day, consider restarting the trace daily to avoid older events accumulating in the same file.


What are the risks of changing oracle sys password? I need to change some old databases SYS password because of security. I'm not DBA of these databases. I don't know if I will change SYS password what it will be happen.

What are the risks of changing oracle SYS password? What will be happen and how can I back to old password if I get some error?

I don't know current passwords, just I have root passwords of database machines(RAC / 2 machines).

Thank you.

There should not be any problems. Nobody should use this password for application purposes. In case of RAC cluster do not forget to change the password on each node. This password is not stored in the database but in local node's password file.

Unless you use Oracle Data Guard (physical replication). For some reason having the same password on each side is not sufficient and you must copy the amended password file to the other site too.


Update field in SQL Server Table Dynamicaly I need to do a 1 time update of a Sql Server table.

The table has a unique ID, contains a member number field and a FileName field which tells us what file the member data came from.

What I am needing to do is update the [Filename] value to be different for all the records pertaining to a specific member number that was present in the historical file more than once.

So in the image below Member number 123 has 3 records in the table that have FileName value as history. I need the value of Filename to be unique per member number record so I would need the Filename values for this member to be History, History_2 and History_3, instead of History for all 3 records.

I'm pretty sure this is possible to do with SQL and am currently working on it but was wondering if someone has come across a similar table update need and had some advise or code I can slightly modify to get my desired results.

This is the code I use to identify the member number that have records that need to be updated:

SELECT Member_Number, COUNT(*) CountNumber
                   FROM mytable
                   WHERE [FileName] = 'History'
                   GROUP BY Member_Number
                   HAVING COUNT(*) > 1

Progress Update:

I got the below code so far but I am having to specify the actual member number. I guess I need to find a way to make the below code loop through all the member numbers. There are about 1,000+ member numbers that need to be updated in the table.

  SELECT *, t1.[FileName] + '_' + CAST(RN AS VARCHAR(50)) FROM
  (
  SELECT (ROW_NUMBER() OVER (ORDER BY(SELECT 1))) RN ,* FROM mytable WHERE Member_Number = 'BAY391'
  ) AS t1
  WHERE RN > 1

If your table is test, try this:

SELECT t1.*
    ,New_FileName = t1.FileName + CASE
        WHEN t2.seq > 1
            THEN '_' + ltrim(str(seq))
        ELSE ''
        END
FROM test t1
JOIN (
    SELECT ID
        ,Memeber_Number
        ,FileName
        ,seq = ROW_NUMBER() OVER (
            PARTITION BY FileName
            ,Memeber_Number ORDER BY ID
            )
    FROM test
    ) t2 ON t1.id = t2.id;


SYS.DBMS_INTERNAL_LOGSTDBY causes error on dump import I'm having problems importing a dump into an Oracle XE Database.
The import worked for several other people, so I can assume that the dump is not corrupt.

In ran the following command:

  C:\Users\franzm>impdp abcac@xe DIRECTORY=exp_dir
  DUMPFILE=expdp_abc_20140122010001.dmp schemas=abc LOGFILE=log.log

exp_dir was set before to the folder with the dump file

I'm getting the following error:

  ORA-31626: job does not exist ORA-04063: package body
  "SYS.DBMS_INTERNAL_LOGSTDBY" has errors ORA-06508: PL/SQL: could not
  find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"
  ORA-06512: at "SYS.KUPV$FT", line 991 ORA-04063: package body
  "SYS.DBMS_LOGREP_UTIL" has errors ORA-06508: PL/SQL: could not find
  program unit being called: "SYS.DBMS_LOGREP_UTIL"

I searched the net and found some topics in the oracle board, but those didn't helped me.
Anyone has a hint?


creating a Pie Chart in SSRS how can I create the pie chart that displays the Count of columns with filters like (  Active Paths / Courses / Sections /----- this is my column name )from 4 different tables and combined them in one query?

If you add more information on how your schema is structured then we can provide a more specific answer. It's likely you'll want to JOIN your data together into one dataset to consume in your SSRS. Though depending on how your Tables are structured, you may want to UNION it too.
If you're using Microsoft SQL Server, you can use a View, or even better a Stored Procedure (since you'll be filtering and parameters will be helpful here).
Then you need to setup a new SSRS and configure how the filters for your stored procedure's parameters are shown in the report.
Finally you can add a Pie Chart via this Microsoft Tutorial: Add a Pie Chart to Your Report (Report Builder).


IORA-00904: Invalid Identifier when converting Radians to Degrees I am using this simple query in Oracle SQL Developer to basically convert a value in Radians to Degrees but I get error at the function itself.

SELECT Degrees(1.571) as ANGLE FROM DUAL;

  Error: Ora-00904: "DEGREES": invalid identifier
  00904. 00000 - "%s: invalid identifier"

I checked Oracle documentation and a function called DEGREES does exist. I tried changing the query to convert DEGREES to RADIANS but same error.

Can someone please advise what's wrong here? The database I am connecting to is Oracle 12cR1.

Thanks in advance.

The DEGREES function you refer to is not in the Oracle database code.  It is in the JavaDB which is also known as Derby.  There is a construct in MySQL called DEGREES so this may be what you were thinking of.

You can make your own function like this

CREATE OR REPLACE FUNCTION radians_to_degree(p_radian IN NUMBER)
   RETURN NUMBER IS
BEGIN
   RETURN p_radian * 0.0174533;
END radians_to_degree;


What is the best way to represent the relationship between Team and Player in an ERD? I am working on designing an entity-relationship diagram (ERD) for a football system, I stopped in the relationship between the Player and Team entity types, as we know a player plays for only one team and a team have many players, so the relationship will be:

Team 1…* Player

But, how can I represent this relation when a player can play in more than one team? In real life, a player has a contract with a team, when that contract ends, the player may continue with the same team or with other team, so the relation will be

Team *…* Player, or
Team 1…* Contract *…1 Player

Questions

Is my understanding is well?
Do you have any suggestions about the two scenarios?

If you want a history of the all the players that have passed in that team the approach: Team 1:N Contracts N:1 Players is fine.
Otherwise if you just want to know the players that are currently in the team and contract is not a needed information for you, the approach: Team 1:N Player will also work


SSRS - Set Page Break within table I had to modify a report that was assembled entirely within table (with 1 column and 10 rows). After my modifications, the report now spans multiple pages and I need it to break naturally at one point (i.e. beginning of Row 8).

Is there a way to add a page break in the middle of a  table?

Note, I've tried adding a "Rectangle" and applying the page break with no luck (in the desired Row and then tried in the Row above).

Additional Info:
   - using Visual Studio 2010
   - DBMS = SQL Server 2012 R2

Any assistance would be greatly appreciated.

If you can group your data in a way that makes row 1-8 group 1 and the other rows group 2 then you can do a page break at the end of each group.


how to upgrade postgresql 9.0.4 to latest with minimum downtime I have master-slave-slave setup for postgresql 9.0.4 in our production setup.
We need to upgrade it to latest (11.X), so my question is what would be the procedure to do so with as little downtime as possible.

Also what options (method wise) i have to upgrade it.

The fastest way will be pg_upgrade with the -k option.

This way. you can get away with a few minutes down time, which is required to move the database metadata.

If you cannot afford a few minutes down time, you will have to resort to a trigger based replication method like Slony. This way, you can replicate the database to a current version and switch the application over once replication has caught up.


ERROR: text search configuration name "english" must be schema-qualified Trying to do some full text searching with postgres and when running the following command

UPDATE "EMAIL" SET PostText = to_tsvector('pg_catalog.english', "TEXT");

I'm getting the error:

ERROR:  text search configuration name "english" must be schema-qualified

Is there something I'm missing?  I've also tried

UPDATE "EMAIL" SET PostText = to_tsvector('english', "TEXT");

Edit 1:
Table EMAIL has the column "TEXT" in it.  They are both in all caps FWIW

Double quotes are for case sensitive identifiers or one's that have whitespace in them. Single quotes are for strings.

Try this:

UPDATE "EMAIL" SET PostText = to_tsvector('english', 'TEXT');

You can also remove the quotes around "EMAIL" for a case insensitive match on the field name as well:

UPDATE email SET PostText = to_tsvector('english', 'TEXT');


json_agg with dynamic 0-level key I'm migrating a schema from NoSQL to postgres. Today's object "b" exists in NoSQL as an element of object a & looks in source like this...

{
  "a": {
    "id": 1,
    "b":{
      "c1": {
        "d1": 1,
        "d2": 2
      },
      "c2": {
        "d1": 1,
        "d2": 2
      },
      "c3": {
        "d1": 3,
        "d2": 4
      }
    }
  }
}

...which I've tentatively assigned to a schema resembling the below ERD

+---+    +-----+    +---+    +---+
| b |<--<| b_d |>-->| d |>-->| c |
+---+    +-----+    +---+    +---+

full db_fiddle

I'd like to be able to recompose the normalised data into the original object but I've been unable to do so. Instead of getting "c" objects as direct key-value pairs, I'm getting whole objects. For example...

select
    b_d.b_id,
    json_agg(json_build_object(c.name,json_build_object('d1',d.d1,'d2',d.d2))) as b
from b_d
join d on d.id = b_d.d_id
join c on c.id = d.c_id
group by b_d.b_id;

...returns...

[{
    "c1 ": {
        "d1": 1,
        "d2": 2
    }
},
{
    "c2 ": {
        "d1": 1,
        "d2": 2
    }
},
{
    "c3 ": {
        "d1": 3,
        "d2": 4
    }
}]

...which you will note has an additional nesting level versus the source object. I suspect I've gone wrong out of the gate with json_build_object but so far have been unable to successfully reconstruct the "b" object as it appears in the original json document.

What do I modify about the query and/or the underlying schema to normalize and denormalize this object?

Instead of looking on the json functions docs page, look instead on the aggregate functions docs page. jsonb_object_agg() will aggregate the object without an additional level of nesting as seen in this demo.

select
    b_d.b_id,
    jsonb_object_agg(c.name,json_build_object('d1',d.d1,'d2',d.d2)) as b
from b_d
join d on d.id = b_d.d_id
join c on c.id = d.c_id
group by b_d.b_id;

...returns the original object as desired.

{
    "c1 ": {
        "d1": 1,
        "d2": 2
    },
    "c2 ": {
        "d1": 1,
        "d2": 2
    },
    "c3 ": {
        "d1": 3,
        "d2": 4
    }
}

See also


Delete User View named the same as system view I am exporting a database to Azure and have found a user view created in a previous version of MSSQL (Currently running 2008) that is name INFORMATION_SCHEMA.TABLE_CONSTRAINTS which obviously should be impossible.  I can find the objectid in sysobjects but can find no way to drop the view by anything other than it's name.

Trying to drop the view this error is generated which makes sense:

  Msg 3701, Level 11, State 5, Line 1 Cannot drop the view
  'INFORMATION_SCHEMA.TABLE_CONSTRAINTS', because it does not exist or
  you do not have permission.

But the object in sysobjects and it's corresponding definition are definitely user based, not what one should expect.

Any thoughts?

Pics:

Making a guess with no information but is the name in sys.objects INFORMATION_SCHEMA.TABLE_CONSTRAINTS?

Try running this:

SELECT object_schema_name(object_id) AS SchemaName, name
FROM sys.all_objects
WHERE name like '%TABLE_CONSTRAINTS'

I'm guessing you get something like this:

SchemaName          Name
------------------  ------------------------------------
INFORMATION_SCHEMA  TABLE_CONSTRAINTS
dbo                 INFORMATION_SCHEMA.TABLE_CONSTRAINTS

First confirm that the second entry is actually a view then run the following:

DROP VIEW [dbo].[INFORMATION_SCHEMA.TABLE_CONSTRAINTS]

Please run the following query and post a screenshot of the results in your question.

SELECT object_id, schema_id, object_schema_name(object_id) AS SchemaName, name, type
FROM sys.all_objects
WHERE name like '%TABLE_CONSTRAINTS'


when we restore and when we recover a database? Restoring involves copying backup files from a secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.
Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.
Question: Why copying/moving a database to a new location is a restore not a recover?

Because, as the Restore and Recovery Overview doc says:

Recovery is the process used by SQL Server for each database to start
in a transactionally consistent - or clean - state.

The recovery process is responsible for bringing a consistent database back online, and the database doesn't have to be moved or restored from a backup for that action to happen (a crashed database would trigger a recovery before coming back online). After a backup is restored a recovery should happen to guarantee a consistent database. Restore and recovery walk close together.

If you have a SQL Server in your desktop (or a lab environment that you can restart), follow these steps:
Recovery:

Restart SQL Server service;
Connect to SQL Server and check SQL Server Logs: Management > SQL Server Logs;
On the Current log, look for a message like this:

Recovery is complete. This is an informational message only. No user
action is required.

You didn't do any restore of a backup, yet a recovery just happened
because SQL Server does it before bringing a database online.

Restore:

Create a folder C:\BackupSQL on the computer SQL Server is
installed;

Connect to SQL Server and run the following:
EXEC sp_cycle_errorlog; --this will help you better see the relevant information for your question

CREATE DATABASE Lab;

BACKUP DATABASE Lab TO DISK = 'C:\BackupSQL\Lab.bak';
BACKUP LOG Lab TO DISK = 'C:\BackupSQL\Lab.trn';

DROP DATABASE Lab;

RESTORE DATABASE Lab FROM DISK = 'C:\BackupSQL\Lab.bak' WITH NORECOVERY;

Check the current log to see messages like:

The database 'Lab' is marked RESTORING and is in a state that does not
allow recovery to be run.
Parallel redo is started for database 'Lab' with worker pool size [4].
1 transactions rolled forward in database 'Lab' (5:0). This is an
informational message only. No user action is required. 0 transactions
rolled back in database 'Lab' (5:0). This is an informational message
only. No user action is required. Restore is complete on database
'Lab'.  The database is now available.

As you can see, you issued a RESTORE and SQL Server executed the
restore and a recovery process to guarantee a consistent
database in the end of your request.

Consistency:
Imagine a situation where you owe me U$$ 500.00 and you're going to transfer the money from your bank account to mine:

Connected to SQL Server run these commands:
USE Lab;
CREATE TABLE BankAccount(userName varchar(50), myMoney money);

INSERT INTO BankAccount(userName, myMoney)
VALUES ('Ronaldo', 1000.00), ('learn9909', 1000.00);

SELECT userName, myMoney FROM BankAccount;

As you can see, each of us has U$$ 1000.00;

Run the commands to transfer the money:
BEGIN TRAN
    UPDATE BankAccount SET myMoney = myMoney - 500.00 WHERE userName = 'learn9909';
    WAITFOR DELAY '02:00'; --it waits for 2 hours before proceding
    UPDATE BankAccount SET myMoney = myMoney + 500.00 WHERE userName = 'Ronaldo';
COMMIT

Open another tab on Management Studio and execute this query:
SELECT userName, myMoney FROM BankAccount WITH (NOLOCK);

You can see the U$$ 500.00 was reduced from your account, but it
hasn't been added to mine yet;

Now let's cause a database crash by abruptly ending SQL Server service: start menu > run > taskmgr. Look for the process called something like SQL Server
Windows NT and end it;

That transaction was interrupted in the middle and it won't be
finished when SQL Server service is available again. You're in big
trouble if it stays like that because U$$ 500.00 was taken from your
account and you still owe me U$$ 500.00 and I can prove it with my
bank statement. To avoid inconsistencies like that SQL Server executes the recovery process before bringing a database online.

To start SQL Server service again and check our bank accounts start menu > run > services.msc, look for SQL Server service and start
it;

Query the table again and feel relieved that the recovery process
did it's job and put the database in a consistent state where that
half transaction was undone:
SELECT userName, myMoney FROM BankAccount;

You can check SQL Server Log again and see that process, but once
again, no backup was restored, only a recovery process occurred.


Extended Event definition information for a stopped session I am trying to get configuration information, via T-SQL, about existing, stopped extended-event sessions.  If the session is started, then the sys.dm_xe_session_object_columns DMV gives me exactly what I'm looking for.  Is that information available somewhere for stopped sessions?

Extended event catalog views fall under both sys.dm_xe* and sys.server_event_* views. The "active" or "running" event sessions have information stored in sys.dm_xe* (also global package metadata) views and overall server defined metadata stored in sys.server_event_* views regardless of state.

Specifically since the question was about information on extended event sessions that aren't currently running, the metadata for that still exists and the basics of it can be found in sys.server_event_sessions catalog view. Joining this with other catalog views such as sys.server_event_session_events, sys.server_event_session_actions, and sys.server_event_session_targets (to name a few) can return most of the information that should be needed.


pg_basebackup with tablespaces defined I have a database with the following tablespaces:

pgwb=# \db
                   List of tablespaces
    Name    | Owner |              Location
------------+-------+-------------------------------------
 bspace     | pgwb  | /data1/tablespaces/TS_B_SPACE/tests
 meta       | pgwb  | /data1/tablespaces/metadata/tests
 mspace     | pgwb  | /data1/tablespaces/TS_M_SPACE/tests
 nspace     | pgwb  | /data1/tablespaces/TS_N_SPACE/tests
 pg_default | pgwb  |
 pg_global  | pgwb  |
 uspace     | pgwb  | /data1/tablespaces/TS_U_SPACE/tests
(7 rows)

They are referenced in as symbolic links in:

pgwb@BLIXSQL0:/data1/pgwb/pg_tblspc> ls -l /data1/pgwb/pg_tblspc
total 0
lrwxrwxrwx 1 pgwb users 35 May  9 09:17 16536 -> /data1/tablespaces/TS_U_SPACE/tests
lrwxrwxrwx 1 pgwb users 35 May  9 09:17 16537 -> /data1/tablespaces/TS_B_SPACE/tests
lrwxrwxrwx 1 pgwb users 35 May  9 09:17 16538 -> /data1/tablespaces/TS_N_SPACE/tests
lrwxrwxrwx 1 pgwb users 35 May  9 09:17 16539 -> /data1/tablespaces/TS_M_SPACE/tests
lrwxrwxrwx 1 pgwb users 33 May  9 09:17 16540 -> /data1/tablespaces/metadata/tests

I have setup the database for PIT recovery.

To make a base backup I use pg_basbackup like:

pg_basebackup --xlog --format=t -D /data1/backup/`date +%Y%m%d`

In the backup folder I have the following files:

pgwb@BLIXSQL0:/data1/backup/20170510> ls -l
total 62772
-rw-r--r-- 1 pgwb users   289792 May 10 13:51 16536.tar
-rw-r--r-- 1 pgwb users  2821120 May 10 13:51 16537.tar
-rw-r--r-- 1 pgwb users  2821120 May 10 13:51 16538.tar
-rw-r--r-- 1 pgwb users   371712 May 10 13:51 16539.tar
-rw-r--r-- 1 pgwb users   716800 May 10 13:51 16540.tar
-rw-r--r-- 1 pgwb users 57170432 May 10 13:51 base.tar

Now a crash happens and I have to restore from these tar files, but I don’t know the symbolic link destination.

How can I solve this? Do I need a pg_dump from the tablespaces?

You will find a mapping file named tablespace_map in your base.tar file.

Here's the documentation:

  When tar format mode is used, it is the user's responsibility to unpack each tar file before starting the PostgreSQL server. If there are additional tablespaces, the tar files for them need to be unpacked in the correct locations. In this case the symbolic links for those tablespaces will be created by the server according to the contents of the tablespace_map file that is included in the base.tar file.

Link: https://www.postgresql.org/docs/9.6/static/app-pgbasebackup.html


Why would someone put `where 1=1` in a query? I came across a view in our database today where the first statement in the where clause was where 1 = 1. Shouldn't this return true for every record? Why would someone write this if it isn't filtering any records?

Some dynamic query builders include this condition so that any "real" conditions can be added with an AND without doing a check like if (first condition) 'WHERE' else 'AND'.


Passing parameters to a procedure executed by DBMS_SCHEDULER I have a scheduler program that runs a stored procedure that requires input parameters.

BEGIN
  DBMS_SCHEDULER.DROP_PROGRAM
    (program_name          => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
/
BEGIN
  SYS.DBMS_SCHEDULER.CREATE_PROGRAM
    (
      program_name         => 'MYSCHEMA.EXPORT_SCHEMA_STARTING'
     ,program_type         => 'STORED_PROCEDURE'
     ,program_action       => 'MYSCHEMA.EXPORT_STATUS'
     ,number_of_arguments  => 0
     ,enabled              => FALSE
     ,comments             => NULL
    );

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
/

How can I pass parameters to the EXPORT_STATUS procedure?

I changed it into program that runs a PL/SQL BLOCK so far:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_PROGRAM
    (
      program_name         => 'MYSCHEMA.EXPORT_SCHEMA_STARTING'
     ,program_type         => 'PLSQL_BLOCK'
     ,program_action       => 'MYSCHEMA.EXPORT_STATUS(''STARTING''); END;'
     ,number_of_arguments  => 0
     ,enabled              => FALSE
     ,comments             => NULL
    );

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
/

It works as I expected. But if anyone knows how to keep it in the previous format, will be appreciated.

When you create a program with CREATE_PROGRAM procedure, you can specify how many arguments it expects with number_of_arguments parameter.

If, for some reason, you want to modify some attribute (number_of_arguments in our case) for an existing Scheduler object (program in our case), you don't need to drop the object and re-create it with new attribute value again, you can just use SET_ATTRIBUTE procedure instead:

  dbms_scheduler.set_attribute(
      name => 'my_program'
    , attribute => 'number_of_arguments'
    , value => 1);

Once you've created the program and all of its attributes are set, you can define the arguments of the program (if there are any) with DEFINE_PROGRAM_ARGUMENT procedure:

dbms_scheduler.define_program_argument(
    program_name => 'my_program'
  , argument_position => 1
  , argument_type => 'VARCHAR2'
  , default_value => 'STARTING'
);

Here, we defined the program argument and its default value. We could have left the default value unspecified, but in this case we would have to set the value of the argument using SET_JOB_ARGUMENT_VALUE procedure. For example, if you created the job and specified the associated program as follows:

dbms_scheduler.create_job(
    job_name => 'call_my_program'
  , program_name => 'my_program'
  ...
);

You would have to set the value of the argument in the program associated with the job this way:

dbms_scheduler.set_job_argument_value(
    job_name => 'call_my_program'
  , argument_position => 1
  , argument_value => 'STARTING');

If you have defined the default value of an argument for a program with DEFINE_PROGRAM_ARGUMENT procedure and also set the value of the argument with SET_JOB_ARGUMENT_VALUE procedure, the argument value set with SET_JOB_ARGUMENT_VALUE procedure will override the default argument value set with DEFINE_PROGRAM_ARGUMENT procedure.


Is there anyway to UPDATE a series of columns on a row with a composite type record/row? Take a table foo,

CREATE TABLE foo(a,b,c,d)
AS VALUES
  (1,2,3,4);

I can insert into foo with a row type..

INSERT INTO foo
  SELECT (foo).*
  FROM foo;

INSERT INTO foo
  SELECT (v).*
  FROM ( VALUES (42,42,42,42) )
    AS v;

But I can't update foo with a row type.

-- fails
UPDATE foo SET (a,b,c,d) = (
  SELECT ROW(1,2,3,4)
).*;

-- also fails.
UPDATE foo SET (a,b,c,d) = (SELECT (1,2,3,4));
ERROR:  number of columns does not match number of values
LINE 1: UPDATE foo SET (a,b,c,d) = (SELECT (1,2,3,4));

Is there any syntax to update an entire row with a composite type. The really weird thing is that this works in a trigger with NEW, and I'm trying to write the same code without a trigger.

CREATE OR REPLACE FUNCTION foo()
RETURNS trigger
AS $$
  BEGIN
    NEW=(42,42,42,42);
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql
IMMUTABLE;

CREATE TRIGGER ok
  BEFORE UPDATE ON foo
  FOR EACH ROW
  EXECUTE PROCEDURE foo();

-- The NEW row in trigger gets updated with (42,42,42,42)
-- the UPDATE succeeds; all rows (42,42,42,42)
UPDATE foo
  SET (a,b,c,d) = (1,2,3,4);

You can see here that we can update the row object inside the procedure, and change the update. However, we can't do that in the UPDATE ddl? I would expect the RECORD produced by the same name of the table to work the same way.

-- Such as this,
UPDATE foo
  SET foo = (1,2,3,4);

There are actually lots of posts regarding this..

Support UPDATE table SET(*)=...
WIP patch for multiple column assignment in UPDATE
Supporting multiple column assignment in UPDATE (9.5 project)
Better handling of UPDATE multiple-assignment row expressions

However, according to /u/pstef, one of these is committed, and this syntax now works on HEAD, though @ypercubeᵀᴹ reports it does not work on 9.6.5

UPDATE foo SET (a,b,c,d) = ROW(10,9,8,7);

The docs don't say much about this

  According to the standard, the source value for a parenthesized sub-list of target column names can be any row-valued expression yielding the correct number of columns. **PostgreSQL only allows the source value to be a row constructor or a sub-SELECT. **An individual column's updated value can be specified as DEFAULT in the row-constructor case, but not inside a sub-SELECT.

But you can also see at the syntax for UPDATE

   ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |


Oracle XE 11.2 Export / Import Charset I created a database in oracle XE 11.2 that uses charset AL32UTF8 that I now want to export.

When I perform the export, the resulting data appears in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set.

The server uses AL32UTF8 character set (possible charset conversion).

Why is the export from the database in a different charset than the database itself is using?

The export utility will use the NLS_LANG environment variable specified for the client session.  If all your data can be represented in the Windows-1252 character set, that shouldn't be an issue.  If you want to do the export using the AL32UTF8 character set, you'd need to set the NLS_LANG.  In Windows, that would be something like

c:\> set nls_lang = american_america.al32utf8


What does "Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 64" in the SQL Server Log mean? Every once in a while, I see the error

  Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 64

in the SQL Server log. Oddly this seems to get thrown at off hours where there is no job or backup running at all.

This is a physical install of SQL Server with 128GB RAM. Here are the version details:

  Microsoft SQL Server 2014 - 12.0.4213.0 (X64) Jun 9 2015 12:06:16 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: )

Looking for some guidance.

Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 64

This message means you are getting out of memory error (OOM). SQL Server is looking to allocate 64 KB of memory, but is unable to do so.

  Microsoft SQL Server 2014 - 12.0.4213.0 (X64)

You are on an unsupported service pack level (SP1 + GDR). You should try patching up to the most recent level (SP2 CU13 at the time of writing). See SQL Server 2014 build versions. If it is a bug, it may well have been fixed.

It could also be due to general memory pressure. Check you have max server memory set correctly. For 128GB physical memory, you could probably set this to around 100GB as a starting point, depending on what else is running on the server.

Also check for other activities running at the time e.g. reports, index maintenance, log shipping, etc. Bear in mind that something could be scheduled outside of SQL Server Agent e.g. Agent on another instance, PowerShell, windows task scheduler and so on. It might even be a monitoring tool running some aggregated data collection. In any case, setting SQL Server's maximum server memory configuration parameter appropriately would address this.


Postgres 9.5 import to MSSQL Server 2012 r2 verification issue I have a postgresql table with 8 million rows. I need to copy it over to MSSQL. I do not have access to the console so I am unable to use PG_Dump. I have the ODBC driver, and have successfully linked up the servers. I was then able to copy the table over month by month. This was slow and painstaking. I need to create a query in postgresql that will list the year, month and total rows per month. The date field was actually created as text. I have tried this but do not get the results I am looking for.

SELECT distinct
        date_part('Year',case
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end) as Year
    ,date_part('Month',case
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end)
         as month
    ,count(date_part('year',case
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end)) as "trandate sum"--date_part('Day',case
    --  WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
    --  else
    --  to_date(trandate, 'MM/DD/YYYY')
    --  end))
  FROM SomeSchema.transaction_detail

  Group By trandate
  order by date_part('Year',case
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end)
        ,date_trunc('Mon'  date_part('Month',case
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end))

Is there a better way to do this?

This is more readable and may perform better:

SELECT date_part('year', month) as year,
       date_part('month', month) as month,
       count(*) as "trandate sum"
FROM (
  SELECT date_trunc('month', to_date(trandate, CASE
             WHEN left(trandate, 4) = '2014' THEN 'YYYY-MM-DD' ELSE 'MM/DD/YYYY' END
                    ))::date as month
  FROM SomeSchema.transaction_detail
  ) td
GROUP BY month
ORDER BY 1, 2

Note that you could install PgAdminIII in your windows desktop, and use its pg_dump (in C:\Program Files\PostgreSQL\9.3\bin), or start the console from PgAdmin (menu Plugins/PSQL Console). Depending on the type of data you're trying to import, a CSV format export file could be a good alternative. Otherwise, an SQL file via pg_dump with individual INSERT statements and processed via MSSQL's osql.exe or sqlcmd.exe tools to run it.


Altering nullability of column in SQL Server Say I have the following table:

CREATE TABLE test(test_id int not null identity primary key,
 field1 int not null,
 field2 int
);
CREATE INDEX IDX_test_field1 ON test(field1);
CREATE INDEX IDX_test_field2 ON test(field2);

Now ALTER TABLE test ALTER COLUMN field1 int works, and field1 allows null.

Nevertheless, I cannot change ALTER TABLE test ALTER COLUMN field2 int not null because of

  ALTER TABLE ALTER COLUMN field2 failed because one or more objects
  access this column.

Also, I cannot change field1 back to not null.

However, I can add and then drop check constraint as many times as I want :

ALTER TABLE test  ADD CONSTRAINT CHK_NN_field2 CHECK (field2 IS NOT NULL);
DROP CONSTRAINT CHK_NN_field2` without any problems.

Is it well defined behaviour? Could someone explain why that happens or point me to documentation?

I'm using SQL Server 2008 R2 if that matters.

Thank you.

The relevant documentation is here

  ALTER COLUMN  Specifies that the named column is to be changed or
  altered.

  The modified column cannot be any one of the following:

  Used in an index, unless the column is a varchar, nvarchar, or
  varbinary data type, the data type is not changed, the new size is
  equal to or larger than the old size, and the index is not the result
  of a PRIMARY KEY constraint.
  Used in statistics generated by the CREATE STATISTICS statement unless
  the column is a varchar, nvarchar, or varbinary data type, the data
  type is not changed, and the new size is equal to or greater than the
  old size, or if the column is changed from not null to null. First,
  remove the statistics using the DROP STATISTICS statement. Statistics
  that are automatically generated by the query optimizer are
  automatically dropped by ALTER COLUMN.

In practice it seems that SQL Server does allow some additional cases beyond that mentioned in the documentation however.

As you show in your question ALTER TABLE test ALTER COLUMN field1 int null does in fact work so the restrictions on changes to columns used in indexes appear to be the same as those for user created statistics.

Moreover the caveat mentioned about Primary Keys seems to be untrue also. The following works fine.

CREATE TABLE test2(pk varchar(10) primary key);

ALTER TABLE test2 ALTER COLUMN pk varchar(100) NOT NULL


How to get last truncated time for each tables in SQL Server database I have a SQL Server 2012 database. Is it possible to get last truncated time for all tables in the database?

Note: Recovery mode is SIMPLE in my scenario. Is it possible if I change it to FULL ?

Remember that truncate is an efficiently logged operation.

Since you are running in Simple Recovery mode and provided your T-log is not truncated, you can get it using undocumented fn_dblog:

SELECT [Current LSN]
    ,[Operation]
    ,[Begin Time]
    ,[Transaction ID]
    ,[Transaction Name]
    ,[Transaction SID]
    ,SUSER_SNAME([Transaction SID]) as UserWhoIssuedTruncate
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_BEGIN_XACT' and
    [Transaction ID] IN (
        SELECT [Transaction ID]
        FROM fn_dblog(NULL, NULL)
        WHERE [Transaction Name] = 'TRUNCATE TABLE'
        )
GO

Click here to enlarge

Alternatively, you can run a server side trace or turn on SQL Audit.

Always give minimum permissions (principle of least privilege) to your users.

  Is it possible if I change it to FULL ?

Yes it is (and recovery model should be defined by your business - do you want to have point in time restore? How much data your business can afford  to lose? etc.) You have to start maintaining your T-log by running regular log backups. This will keep your T-log at a sensible size.

Edited
Query uses "IN" instead of "=" as multiple results in the sub-query are possible.


Calculate sum of digits I have a column that contains numbers:

Value
-----
123
452
021
111
...

I want to get the sum of each value's digits.

Answer should look like

Value  SumValue
-----  --------
123    6
452    11
021    3
111    3
...    ...

How can I do this?

I have tried this:

select value
     , case
           when len(value) = 2 then convert(int, left(value, 1))
                                  + convert(int, right(value, 1))
           when len(value) = 3 then convert(int, substring(convert(varchar(1), value), 1,1))
                                  + convert(int, substring(convert(varchar(1), value), 2,1))
                                  + convert(int, substring(convert(varchar(1), value), 3,1))
           else value
       end as SumNum
from get_number (000, 999);

When I get to three digits, it does not calculate correctly, and it seems to me like there's got to be a better solution.

Here's a solution without having to convert the value to strings.

DECLARE @ValueTable TABLE (Value INT)

INSERT INTO @ValueTable (Value)
VALUES
    (5),
    (57),
    (3124),
    (645),
    (312114),
    (9000092)

SELECT
    OriginalValue = V.Value,
    SumOfDigits =
        V.Value % POWER(10, 1) / POWER(10, 0)
        + V.Value % POWER(10, 2) / POWER(10, 1)
        + V.Value % POWER(10, 3) / POWER(10, 2)
        + V.Value % POWER(10, 4) / POWER(10, 3)
        + V.Value % POWER(10, 5) / POWER(10, 4)
        + V.Value % POWER(10, 6) / POWER(10, 5)
        + V.Value % POWER(10, 7) / POWER(10, 6)
        + V.Value % POWER(10, 8) / POWER(10, 7)
FROM
    @ValueTable AS V

Result:

OriginalValue   SumOfDigits
5               5
57              12
3124            10
645             15
312114          12
9000092         20

I'll explain how this works with an example, like value 645. To get the last digit we calculate the rest of the original value when divided by 10 (this is the mod operator %). Since 645 / 10 is 64.5 (it won't show the decimal value if done on SQL Server because it's treated as INT), the rest is 5.

SELECT 645 % 10 -- Result: 5

To get the second to last digit, we calculate the rest of the original value when divided by 100 (100 = 10 * 10 = 10 ^ 2 = POWER(10, 2)). Since the rest of a division by 100 might result in 2 digits (from 0 to 99) and we just need the tens digit, we divide this result by 10. The result of the division will be INT if the original number was INT and this is exactly what we wanted.

SELECT
    645 % 100,      -- Result: 45
    645 % 100 / 10  -- Result: 4 (second to last digit)

To get the 3rd to last, we just add an additional 0 (multiply by 10) to both the mod operator value and the division:

SELECT
    645 % 1000,         -- Result: 645
    645 % 1000 / 100    -- Result: 6 (third to last digit)

This can also be written with the POWER function, which is the expression wrote in the solution.

SELECT
    645 % POWER(10, 3),                 -- Result: 645
    645 % POWER(10, 3) / POWER(10, 2)   -- Result: 6 (third to last digit)

One thing to note in this solution is that you must add as many additions (each with it's own % and /) as you might have digits on the supplied values.

Also, if you want to repeat this process if the resulting number has more than one digit (for example value 993 -> 21 -> 3), there's a very simple and curious algorithm for this that's called the digital root.

Here's a more expressive solution from Andryi M:

SELECT
    OriginalValue = V.Value,
    SumOfDigits =
          V.Value            % 10
        + V.Value / 10       % 10
        + V.Value / 100      % 10
        + V.Value / 1000     % 10
        + V.Value / 10000    % 10
        + V.Value / 100000   % 10
        + V.Value / 1000000  % 10
        + V.Value / 10000000 % 10
FROM
    @ValueTable AS V


Create a "barrier" (prevent parallel execution) in Oracle PL/SQL? I'm maintaining some stored procedures in an Oracle 11g RAC environment that are part of the pipeline of steps for data-interchange with other systems.  One in particular has been producing duplicate records.  Files get loaded into a set of staging-tables by an ETL job,  which then calls this stored procedure, which has no parameters (except an OUT parameter that gets ignored by the calling ETL code,  but that's another issue).  The body of the procedure starts with a statement like

For I in ( SELECT * from  SOME_STAGING_TABLE where status = 'NEW' order by X_ID) LOOP

However, the ETL job that calls this is in turn called by another ETL job that finds all "files" that haven't been processed and calls it, once per file, in parallel.  I think what's happening is:

Master ETL job runs, finds two files,  invokes two worker jobs
Worker ETL job A runs, gets through first phase
Worker ETL job B runs, gets through first phase
Worker ETL job A invokes stored procedure
Stored procedure A' gets two files' worth of records to process
Worker ETL job B invokes stored procedure
Stored procedure B' gets two files' worth of records to process

One solution might be to enforce serialization in the ETL service.  Another might be to change the calling-convention of the stored procedure so it takes a file ID as a parmeter.  However,  let's say I need to do it in the database.  Is there any way to set up a stored procedure so that only one connection can be executing a designated block of code inside it at a time?

(In C or Java this would be called a "mutex" or "synchronized block", and I know MySQL has a "LOCK TABLES" statement that could be used for that purpose;  does Oracle 11g have anything like that?)

Oracle has several locking mechanisms that you can use to serialize operations:

You could create a single-row table that is locked at the beginning of the procedure. The lock will be locked until the transaction ends (commit/rollback).
You could request a lock with DBMS_LOCK.request. This lock can be maintained for the duration of the session or until you call DBMS_LOCK.release. This lock can be maintained across commits.

Example of serialization using DBMS_LOCK:

SQL> CREATE OR REPLACE PROCEDURE serial IS
  2     l_lock_handle  VARCHAR2(128 BYTE);
  3     l_lock_request INTEGER;
  4  BEGIN
  5     dbms_lock.allocate_unique(lockname=> 'MY_SERIAL_PROC', lockhandle => l_lock_handle);
  6     LOOP
  7        l_lock_request := dbms_lock.request(lockhandle        => l_lock_handle,
  8                                            timeout           => 5,
  9                                            release_on_commit => FALSE);
 10        CASE l_lock_request
 11           WHEN 0 THEN
 12              EXIT; -- success
 13           WHEN 1 THEN
 14              dbms_output.put_line('lock already reserved, wait...');
 15              dbms_lock.sleep(5); -- sleep 5 seconds before retrying
 16           ELSE
 17              raise_application_error(-20001, 'Lock error: ' || l_lock_request);
 18        END CASE; --
 19     END LOOP;
 20     ----------------------------------------
 21     -- serialized block of code           --
 22     -- (lock will be kept accross commit) --
 23     ----------------------------------------
 24     dbms_lock.sleep(30);
 25     ----------------------------------------
 26     -- End of serialized code             --
 27     ----------------------------------------
 28     l_lock_request := dbms_lock.release(lockhandle => l_lock_handle);
 29  END;
 30  /

Procedure created

Only one session can run this procedure at the same time:

session1> exec serial;
                                   session2> exec serial;
                                   lock already reserved, wait...
                                   lock already reserved, wait...
                                   lock already reserved, wait...

PL/SQL procedure successfully completed

                                   PL/SQL procedure successfully completed.


Find locations in table that are within a certain radius distance of a given latitude/longitude We have a table named areas with column - id, name, lattitude, longitude and position. The position column is of POINT type in MySQL and has latitude, longitude value.

To get areas within 25 kilometers of given latitude, longitude i.e. (28.638753, 77.073803) and order them based on proximity to this point, we are using the following query as recommended here by Google.

SELECT id, name, ( 6371 * acos( cos( radians(28.638753) ) * cos( radians( Lattitude ) ) * cos( radians( Longitude ) - radians(77.073803) ) + sin( radians(28.638753) ) * sin( radians( Lattitude ) ) ) ) AS distance
FROM areas
HAVING distance < 25
ORDER BY distance asc;

The table is:

CREATE TABLE `areas` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Lattitude` decimal(18,6) DEFAULT NULL,
  `Longitude` decimal(18,6) DEFAULT NULL,
  `position` point NOT NULL,
  PRIMARY KEY (`ID`),
  SPATIAL KEY `sx_areas_position` (`position`)
)

The explain query gives following. Please note that rows = 21750 as it's test data. Actual data on production will be 100k+ rows.

Following is the index information:

This query is really slow when run with > 100k of records (takes up to 500 milliseconds). Is there any way to optimize it i.e.using spatial index?

There are lots of Q&A here.  As a first cut, use a "bounding box".

ANALYZE TABLE and OPTIMIZE TABLE are irrelevant.

You mention Lattitude, but you don't have such a column.

Either go with point and SPATIAL, or go with latitude and longitude; it is probably folly to mix them.


SQL Server Transaction Commits but results are missing I am not sure how to describe the problem, so I will do the best I can and answer any questions.

My project is a Database Project for SQL Server 2008 (client dependent). Within that project I have a series of Seed Scripts that are compiled into a single sql script. The bulk of those seed scripts deal with inserting a large number of VARBINARY records (200MB+ worth of inserts). Since Visual Studio (and most other text editors) have trouble dealing with large files, I have the inserts logically split out into different files. Each file is wrapped in a transaction. The inserts themselves are constructor inserts.

The problem I have is each transaction commits without error. However I am getting the incorrect number of records in the finished database. Or the other effect I am seeing is zero records in the resulting table. While the scripts run, I see records inserted but once the script completes all the records are gone! So ... very confused. Any insight as to why I might be seeing this behavior would be great!

Each is laid out like e.g.

BEGIN TRANSACTION AdBannerBlackEagle
PRINT N'BEGIN TRANSACTION AdBannerBlackEagle'

PRINT N'AdBanner BlackEagle English'

INSERT INTO [dbo].[ContentImageTemplateDetails]
    ([ContentImageTemplateId]
    ,[ContentImageTemplateDetailsCodeKey]
    ,[Data]
    ,[Checksum]
    ,[ContentType]
    ,[RegionID]
    ,[LanguageCode]
    ,[DisplayOrder]
    ,[CreatedDate]
    ,[ModifiedDate]
    )
VALUES
    (
        1,
        'AdBanner'
        ,0xFFD8FFE10018457869660000<reduced for brevity>
        ,'5F44C068DEE6507FDB01F52C9D66C291E06896DAACB2A6D687BDCE0EE3A5CE47'
        ,'jpg'
        ,5
        ,'en-DE'
        ,1
        ,SYSDATETIMEOFFSET()
        ,SYSDATETIMEOFFSET()
    ),
    (
        1,
        'AdBanner'
        ,0xFFD8FFE10018457869660000<reduced for brevity>
        ,'059FF9C3FCAF65EEDC01719D791AE88791C5C1A647BBC7F229A5F3FB0862D54D'
        ,'jpg'
        ,5
        ,'de-DE'
        ,2
        ,SYSDATETIMEOFFSET()
        ,SYSDATETIMEOFFSET()
    )
GO

COMMIT TRANSACTION AdBannerBlackEagle
PRINT N'COMMIT TRANSACTION AdBannerBlackEagle'

You state that:

  each transaction commits without error

I believe there is a nuance here that is being overlooked. The DML statement can cause an error and the COMMIT still happens (hence "nothing" is Committed) because this is not being done in a TRY...CATCH construct (and I assume that you are not setting XACT_ABORT ON at the beginning of the scripts). I believe that the DML statements are getting an error (possibly a truncation one?) but the Commit then runs and succeeds.

AND / OR:

Another possibility is that you have an unmatched BEGIN TRAN in one of the scripts (meaning, no matching COMMIT). Or how exactly are you running these scripts? If there is an open Transaction that is not committed by the time the scripts end, then the Transaction will be automatically rolled-back.

Individual DML / DLL statements, when executed outside of an explicit BEGIN TRAN...COMMIT/ROLLBACK are already their own Transaction and are automatically committed. Hence, you do not need BEGIN TRAN...COMMIT for single statements, unless you have some additional logic after the DML / DDL statement that conditionally decides on COMMIT or ROLLBACK. Also, unless you need the ability to discard portions of work that covers many statements within a single Transaction without rolling back the ALL statements, then you don't need to name them. It looks like you should start by removing the BEGIN TRAN / COMMIT / ROLLBACK statements, but still keep the TRY...CATCH statements that you are adding.