"When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained"? The MSDN online article "Snapshot Isolation in SQL Server" states:

"An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed or another isolation level is set. When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained. Subsequent connections reusing a pooled connection use the isolation level that was in effect at the time the connection is pooled"

Isn't it self-contradictory paragraph ("until" vs. "retained")?

Then, if "the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained" after closing the connection and returning it to a pool, how it should be understood:

that the default isolation level will have arbitrary value (different connections in the pool will have different isolation levels, and its value will depend on the connection being re-opened)?
or all the default values on all connection in thу pool will be changed to last one? but again quite unknown before hand?

A connection from the pool will have the isolation level set by the last client to use that connection. Yes, it really is that scary.

The long and the short of it is that if you change the isolation level of a connection you must explicitly set it back to READ COMMITTED before closing. Better is to explicitly declare your required isolation level at the start of any batch, to ensure your code isn't impacted by somebody else being sloppy, and return it to the default at the end.

This behaviour, while baffling, is apparently by-design.


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

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

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

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

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

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

The config in postgresql.conf on the primary is:

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

The config in recovery.conf on the backup is:

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

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

Solved it. The issue was that I had Monit running in the background which was picking up the backup database before I had written recovery.conf (the servers are being provisioned by chef).

The solution was to pass the -R flag to pg_basebackup in order to ensure the database was never started outside of recovery mode which caused it to be incompatible with the WALs and streaming replication from the master.


JOIN/Union Two tables, removing NULL where possible (similar to pandas concatenate) I am wondering how to perform an operation that I guess is most akin to a concatenate if you're familiar with pandas.

Basically, what I want to do seems like a UNION where records are "collapsed" in a sense to remove NULL values (where say, the ID and DATE are the same). I've tried a UNION, FULL JOIN, and SELF JOIN but none of those seem to produce the desired result. It seems like maybe MSSQL's MERGE is maybe what I'm looking for? It seems like that operation is meant for table creation, however.

My end goal is basically to create a view from the result. All I really need is the SELECT statement that allows me to do further manipulation.

To help visualize the problem, let's say I have the below tables:

+----+--------+-------------+---------------+
| ID | Date   | FloorRating | CeilingRating |
+----+--------+-------------+---------------+
| 1A | 1/1/18 | 9           | 6             |
+----+--------+-------------+---------------+
| 1A | 1/1/19 | 9           | 7             |
+----+--------+-------------+---------------+
| 3D | 2/2/19 | 5           | 8             |
+----+--------+-------------+---------------+
| 3D | 3/3/19 | 4           | 9             |
+----+--------+-------------+---------------+

+----+--------+--------------+-----------+
| ID | Date   | WindowRating | FanRating |
+----+--------+--------------+-----------+
| 1A | 1/1/18 | 4            | 7         |
+----+--------+--------------+-----------+
| 1A | 2/2/19 | 3            | 7         |
+----+--------+--------------+-----------+
| 3D | 2/2/19 | 9            | 4         |
+----+--------+--------------+-----------+
| 3D | 8/8/19 | 1            | 3         |
+----+--------+--------------+-----------+

What I want to end up with is something like this, where all possible ID-Date combinations are accounted for and field values are filled in where possible:

+----+--------+-------------+---------------+--------------+-----------+
| ID | Date   | FloorRating | CeilingRating | WindowRating | FanRating |
+----+--------+-------------+---------------+--------------+-----------+
| 1A | 1/1/18 | 9           | 6             | 4            | 7         |
+----+--------+-------------+---------------+--------------+-----------+
| 1A | 1/1/19 | 9           | 7             |              |           |
+----+--------+-------------+---------------+--------------+-----------+
| 1A | 2/2/19 |             |               | 3            | 7         |
+----+--------+-------------+---------------+--------------+-----------+
| 3D | 2/2/19 | 5           | 8             | 9            | 4         |
+----+--------+-------------+---------------+--------------+-----------+
| 3D | 3/3/19 | 4           | 9             |              |           |
+----+--------+-------------+---------------+--------------+-----------+
| 3D | 8/8/19 |             |               | 1            | 3         |
+----+--------+-------------+---------------+--------------+-----------+

I appreciate any help or tips you can provide!

I've tried a UNION, FULL JOIN, and SELF JOIN but none of those seem to
  produce the desired result

FULL JOIN works fine. Maybe you were joining on the wrong predicate or didn't coalesce the results from the ID and Date columns (done with ISNULL below)?

db <> fiddle

SELECT ISNULL(T1.[ID], T2.[ID]) AS [ID],
       ISNULL(T1.Date, T2.Date) AS Date,
       [FloorRating],
       [CeilingRating],
       [WindowRating],
       [FanRating]
FROM   Table1 T1
       FULL JOIN Table2 T2
              ON T1.[ID] = T2.[ID]
                 AND T1.[Date] = T2.[Date]
ORDER  BY [ID],
          Date


MongoDB 3.6 crashed due to invariant() failure MongoDB crashed twice in 6 hours today due to invariant() failure. We have been using MongoDB 3.6.3 for more than a year now, but this is the first time that we saw this issue. The extract from the log files is given below for reference -

Log from 1st failure:

  2019-02-27T08:44:51.411+0530 E STORAGE  [conn3385299] WiredTiger error
  (12) [1551237291:411637][2344:2000827264],
  file:collection-17055--5635302636308412295.wt, WT_CURSOR.search:
  C:/MREYE/MongoDB/data\collection-17055--5635302636308412295.wt:
  handle-read: ReadFile: failed to read 4096 bytes at offset 720363520:
  Not enough storage is available to process this command.

  : Not enough space 2019-02-27T08:44:51.412+0530 F -
  [conn3385299] Invariant failure: seekRet resulted in status
  UnknownError: 12: Not enough space at
  src\mongo\db\storage\wiredtiger\wiredtiger_record_store.cpp 1734

    2019-02-27T08:44:51.412+0530 F -        [conn3385299]

  ***aborting after invariant() failure

Log from 2nd failure:

  2019-02-27T13:44:11.594+0530 E STORAGE  [conn20] WiredTiger error (12)
  [1551255251:594637][54524:2000827264],
  file:collection-17071--5635302636308412295.wt, WT_CURSOR.search:
  memory allocation of 28696 bytes failed: Not enough space

  2019-02-27T13:44:11.594+0530 F -        [conn20] Invariant failure:
  seekRet resulted in status UnknownError: 12: Not enough space at
  src\mongo\db\storage\wiredtiger\wiredtiger_record_store.cpp 1734

  2019-02-27T13:44:11.595+0530 F -        [conn20]

  ***aborting after invariant() failure

Note:

The MongoDB is on C drive and there is sufficient space left (276 GB) on that drive. The MongoDB data folder size itself is only 65 GB. Any idea what might have caused this issue?

It looks like the problem is not with your physical memory.

What is the RAM size you are using? Are there any other applications that are consuming more RAM memory?

Refer the below thread for more details

Invariant failure: seekRet resulted in status


Help with a pivot query I've got data that goes sort of like this:

Time    Name    country
12      steve   brazil
12      mary    brazil
12      kate    usa
12      john    usa
13      paul    canada
13      tim     canada

How would i go about writing a pivot query so that it looks like this?:

Time    Name1    Name2   Country
12      steve    mary    brazil
12      kate     john    usa
13      paul     tim     canada

Thanks!

Here is a quick example of using min and max along with group by to produce a result that is similar to yours.

--Demo setup
drop table if exists table1
go
CREATE TABLE Table1 (
  Time INTEGER,
  Name VARCHAR(5),
  country VARCHAR(6)
);

INSERT INTO Table1
  (Time, Name, country)
VALUES
  ('12', 'steve', 'brazil'),
  ('12', 'mary', 'brazil'),
  ('12', 'kate', 'usa'),
  ('12', 'john', 'usa'),
  ('13', 'paul', 'canada'),
  ('13', 'tim', 'canada');

--Solution
SELECT [Time]
    ,min(NAME) AS Name1
    ,max(NAME) AS Name2
    ,Country
FROM table1
GROUP BY TIME
    ,country
ORDER BY [Time]

| Time | Name1 | Name2 | Country |
|------|-------|-------|---------|
| 12   | mary  | steve | brazil  |
| 12   | john  | kate  | usa     |
| 13   | paul  | tim   | canada  |


Field order in a composite index order with high selectivity and low selectivity fields I have a SQL Server table with over 3 billion rows. One of my query takes an extremely long time so I am considering optimizing it. The query looks like this:

SELECT [Enroll_Date]
      ,Count(*) AS [Record #]
      ,Count(Distinct UserID) AS [User #]
  FROM UserTable
  GROUP BY [Enroll_Date]

The [Enroll_Date] is a low selectivity column with less than 50 possible values, while the UserID column is a high selectivity column with more than 200 million distinct values. Based on my research I believe I should create a non-clustered composite index on these two columns, and in theory the high selectivity column should be the first column. But I am not sure in my case, would that work because I am using the low selectivity column in the group by clause.

This table has no clustered index.

As an alternative to @AaronBertrand's solution (if you can't or don't want to create an indexed view), I would recommend you to create an index on (Enroll_Date, UserID). If this type of question is very common on your table, this should probably even be your clustered index.

I would not generally recommend high-selectivity indexes as a general "best practice", but rather look at what index will give your query the best performance.

An index on (Enroll_Date, UserID) will give your query a highly optimized, non-blocking query plan with Stream Aggregates.

"Non-blocking" in this context means that the query doesn't need to buffer any significant amounts of data (like, for instance, a sort or hash aggregate would), which means it (a) starts returning rows immediately, and (b) consumes practically no working memory.


Check if restaurant sells only the pizzas that customer likes using SQL Tables for SQL query

1. Likes(cname, pizza)

2. Customers(cname, area)

3. Restaurants(rname, area)

4. Sells (rname, pizza, price)

Condition:

Customer likes all the pizza in the restaurant
Restaurant has the same location as customer

Result: List of customers that fulfill the conditions

My attempt:

    SELECT DISTINCT Customer (
    FROM Sells S, Likes L, Restaurant R, Customer C
    WHERE C.area = R.area     -- Check whether location of customer = restaurant
    AND L.pizza = S.pizza     -- But i'm not clear how to check if the restaurant sells only the pizzas that the particular customer likes.
    );

I'd logically do (2) first with a join ("Restaurant has the same location as customer"), and then eliminate the results with a restaurant that sells a pizza that the customer doesn't like:

create table customers(
  cname text
 ,area text
);

insert into customers(cname,area)
values('alice', 'east')
     ,('bob', 'north')
     ,('james', 'central');

create table restaurants(
  rname text
 ,area text
);

insert into restaurants(rname,area)
values('Pizza Place', 'east')
     ,('Pizza Local', 'west')
     ,('Pizza World', 'central');

create table sells(
  rname text
 ,pizza text
 ,price int
);

insert into sells(rname,pizza,price)
values('Pizza Place', 'hawaiian','10'),
  ('Pizza Local', 'pepperoni','11'),
  ('Pizza World', 'chicken','12'),
  ('Pizza World', 'meat feast','13'),
  ('Pizza World', 'veggie','8');

create table likes(
  cname text
 ,pizza text
);

insert into likes(cname,pizza)
values ('alice', 'hawaiian')
      ,('bob', 'pepperoni')
      ,('james', 'chicken')
      ,('james', 'meat feast');

select *
from customers c join restaurants r using (area)
where not exists( select pizza from sells where rname=r.rname
                  except
                  select pizza from likes where cname=c.cname );

area | cname | rname
:--- | :---- | :----------
east | alice | Pizza Place

dbfiddle here

To get the list of customers just change select * to select distinct cname


calculate row wise total sum SELECT
 RegionManager,
 [Face-to-Face],
 [Phone],
 [Face-to-Face]+[Phone] as[Grand Total]
FROM
(select
 ed.FRST_NM + ' ' + ed.LAST_NM AS RegionManager
 ,ACTVTY_TYP
 ,CALL_ID
 --,Month(CALL_DT) callmonth
FROM CODS_EDW.dbo.TERR_HIER th
LEFT JOIN CODS_EDW.dbo.[EMP_ROSTR] er ON er.[GEOG_ID] = th.GEOG_CD
LEFT JOIN CODS_EDW.dbo.EMP_DIM ed ON ed.EMPL_ID = er.EMPL_ID
LEFT JOIN CODS_EDW.dbo.[CALL] c
ON  c.TERR_CD = GEOG_CD + ' ' + GEOG_NM
WHERE th.SF_ID = 4
AND th.GEOG_LVL = 3
) source
PIVOT
(
    COUNT(CALL_ID)
    FOR ACTVTY_TYP
    IN ([Face-to-Face],[Phone] )
) AS pvtMonth
order by RegionManager

Using pivot i got result like this:

RegionManager   Face-to-Face    Phone   Grand Total
Craig Irwin     73  6   79
Holly Murphy    27  124 151
Julie Brown     37  28  65
Kevin Borth     52  35  87
Linda Krueger   46  23  69
Robert Bell     66  21  87

I want the output like below.(The columns might change dynamically, needs to calculate Sum by row wise)

Row Labels  Face-to-Face    Phone   Grand Total
Craig Irwin     73  6   79
Holly Murphy    27  124 151
Julie Brown     37  28  65
Kevin Borth     52  35  87
Linda Krueger   46  23  69
Robert Bell     66  21  87
Grand Total     301 237 538

you have to put your query in CTE for it you have to use  ROLLUP

;WITH CTE AS
(
-- Your Query
)

then

SELECT
  RowLabels = ISNULL(RowLabels, 'Grand Total'),
  FacetoFace = SUM(FacetoFace),
  Phone = SUM(Phone),
  GrandTotal = SUM(GrandTotal)
FROM CTE
GROUP BY ROLLUP(RowLabels);

SAMPLE DEMO

declare @temp table (RowLabels nvarchar(max), FacetoFace int, Phone int, GrandTotal int)

insert into @temp values ('Craig Irwin  ',73,6  ,79)
insert into @temp values ('Holly Murphy ',27,124,151)
insert into @temp values ('Julie Brown  ',37,28 ,65)
insert into @temp values ('Kevin Borth  ',52,35 ,87)
insert into @temp values ('Linda Krueger',46,23 ,69)
insert into @temp values ('Robert Bell  ',66,21 ,87)

SELECT
  RowLabels = ISNULL(RowLabels, 'Grand Total'),
  FacetoFace = SUM(FacetoFace),
  Phone = SUM(Phone),
  GrandTotal = SUM(GrandTotal)
FROM @temp
GROUP BY ROLLUP(RowLabels);


Compare a date withing range of date fields in mysql I have this simple query that retrieving data:

select leave_detail_id,
       emp_id,
       leave_type,
       leave_current_status_id,
       leave_start_date,
       leave_end_date
from tbl_emp_leave_details eld

Data:

Now I have this query, i want to filter my record based on date:

select leave_detail_id,
       emp_id,
       leave_type,
       leave_current_status_id,
       leave_start_date,
       leave_end_date
from tbl_emp_leave_details eld
where leave_start_date >= '2019-09-01'
and   leave_end_date <= '2019-09-31'

Now it should bring the highlighted record but it shows nothing (no record found). What should I do bring the highlighted record? what is the problem with my query?

If you allow partial overlapping (not full include only as your code did) then you must compare (start1 with end2) and (start2 with end1):

where leave_end_date >= '2019-09-01'
  and leave_start_date <= '2019-09-31'

If you need the length of overlapping period then you need in

select datediff(greatest(leave_start_date, '2019-09-01'),
                least(leave_end_date, '2019-09-31'))


Postgres client copy (\copy) command doesn't have access to a temporary table? I'm generating a list of SQL commands to export some data that I ultimately run using psql -f. The queries all get the same subset of data, so I thought I'd factor the qualifications out and put a list of eligible user ids in a temporary tables like so

create temporary table tmp_export_users as (select id from users where ...)

then refer back to that in my \copy commands like

\copy (select ... from table where user_id in (select id from tmp_export_users)) TO 'filename.csv' WITH CSV HEADER

Those are all in the same file, one per line, and running them -f I get the error that the copy commands can't see the temporary table, so I'm guessing that the client copy command must not actually use the same postgres session as psql.

Is that correct? Is there a way to change that behavior?

\copy can use a temporary table.

First I tested and confirmed this with version 9.0 at the command line.
Then I created a file with SQL and psql meta command \copy using multiple temporary tables. That worked for me, too.

CREATE TEMP TABLE tmp as SELECT * FROM tbl;
\copy (SELECT * FROM tmp JOIN tbl USING (id)) TO '/var/lib/postgres/test1.csv';

Call:

psql -p5432 mydb -f test.sql

Note the terminating semicolon, which is optional at the end of a file (terminated implicitly), but required after any other SQL statement and also after the last one if executed in psql interactively.

Normally, psql meta-commands cannot be mixed with SQL on the same line in a file executed per psql -f. I quote the manual on psql:

  Parsing for arguments stops at the end of the line, or when another
  unquoted backslash is found. An unquoted backslash is taken as the
  beginning of a new meta-command. The special sequence \\ (two
  backslashes) marks the end of arguments and continues parsing SQL
  commands, if any. That way SQL and psql commands can be freely mixed
  on a line. But in any case, the arguments of a meta-command cannot
  continue beyond the end of the line.

Different rules apply after \copy, though. Essentially, psql switches back to SQL mode automatically after \copy See:

How to update selected rows with values from a CSV file in Postgres?

But you wrote you had all commands on separate lines. So that cannot be the explanation in your case.

All that aside, have you considered using COPY (the SQL command) instead of \copy (the psql meta-command)?

Of course, the target file would have to be local to the server not the client in this case. And different file privileges apply. The manual:

  Files named in a COPY command are read or written directly by the
  server, not by the client application. Therefore, they must reside on
  or be accessible to the database server machine, not the client. They
  must be accessible to and readable or writable by the PostgreSQL user
  (the user ID the server runs as), not the client.


Failed to connect to oracle SQL Plus I tryed to connect to SQL Plus as the admin "System", I'm sure that the password I wrote was correct but the connection failed. I also tried to connect with Scott/tiger but it failed as well.
What can be the problem, and how to resolve it please ?

this is what I get after trying to connect using both System and Scott:

Restart your database and that may clear up your issue.  Any more relevant advice will require alert logs or trace files from the database.


Postgres handle case to continue Insert other row if some row doesn't exists I am trying to insert data to a table using below query:
INSERT INTO projects_users (user_id, project_id, is_admin) VALUES (
            (Select id from users where username='example'),
            (Select id from projects where name='example'),
            True
        ),(
            (Select id from users where username='example'),
            (Select id from projects where name='example'),
            True
        )

This query fails when some user is not there in users table (Select id from users where username='does_not_exists').
I want to handle such cases, and skip such rows (where username doesn't exists) and continue adding other rows.
In case of duplicate rows, I know we can do "On Conflict Do Nothing". Is there something similar available for cases like this? And is it a good practice to do?

Build a SELECT expression that does what you want, and then use INSERT INTO ... SELECT ... to insert it into the table.
To get a SELECT that does what you want, wrap the VALUES expression in parentheses, add an alias with field names for your sub-selects, then add a WHERE for the project and user IDs being NOT NULL. The final query becomes:
insert into projects_users select * from
(VALUES (
            (Select id from users where username='user_id1'),
            (Select id from projects where name='project_id2'),
            True
        ),(
            (Select id from users where username='user_id3'),
            (Select id from projects where name='project_id4'),
            True
        )
) as r(user_id, project_id, is_admin)
WHERE user_id is not null and project_id is not null;


how to convert xml to database table input:
Declare @OUT xml
select @OUT = N'
<row>
  <kind>MainCat</kind>
  <sortid>1</sortid>
  <kind_id>1</kind_id>
  <PPoet>حافظ</PPoet>
  <MTitle>حافظ</MTitle>
  <Row>1</Row>
</row>'

select T.X.value('@Kind', 'nvarchar(50)') as kind,
T.X.value('@sortid', 'int') as sortid
from @out.nodes('/row') as T(X)

this select return NULL!

Very close:

select
  T.X.value('./kind[1]', 'nvarchar(50)') as kind,
  T.X.value('./sortid[1]', 'int') as sortid
from @out.nodes('/row') as T(X);


Do I need to worry about RESTORE FILELISTONLY types other than L and D? According to https://msdn.microsoft.com/en-us/library/ms173778.aspx, there are four types, L, D, F and S.

All of the actual database instances I've seen so far (SQL Server 2008 and up) only have types L and D. I've written a script to backup and restore databases very quickly (there are a wealth of answers on Stack Overflow with instructions) but I'm wondering if I'll ever need to worry about the other two types: F and S.

I've read that full-text catalogues (F) are virtual from SQL Server 2008 onwards, so it seems like I will never encounter those. The documentation says that S can be an in-memory OLTP container, but I'm not sure if FileStream and FileTable are also virtual or not.

In SQL Server, the full-text catalog is a logical concept and does not reside in a filegroup. Therefore, to back up a full-text catalog in SQL Server, you must identify every filegroup that contains a full-text index that belongs to the catalog. Then you must back up those filegroups, one by one.
When a full-text index is created, it is placed in one of the following locations:
A user-specified filegroup.
The same filegroup as base table or view, for a nonpartitioned table.
The primary filegroup, for a partitioned table.

Source
If the database has full text catalog,  you'll need to backup the filegroups pertaining to it. It is possible that there is a filegroup only containing the index for the full text catalog and nothing else, as seen by "A user-specified filegroup".

If a full-text index resides in a separate filegroup from all of the associated table data, the behavior of piecemeal restore depends on which of the filegroups is restored and brought online first:

Source
Now for filestream, you can see an example of backups and restores here:
MSSQL
You can see that the filestream backup data is classified as type S as you talked about. It is also seen in SQL as FILESTREAM type and is it's own file to be restored.
To recap, you may run into F and S type in the filelist. S type seems more likely as it's possible the full text indices reside in a filegroup with tables or other data and would be seen as D. You can specify your own filegroup for full text indices that contain just the indices however.


Oracle create user with privileges to grant references I want to create an Oracle user who can grant references to other users.
Do I need to make him sysdba for that, or is there any other way?

You don't need to grant SYSDBA privileges, and shouldn't unless really necessary. You should follow the principle of least privilege. From Oracle's security guidelines:

  Do not provide database users or roles more privileges than are
  necessary. (If possible, grant privileges to roles, not users.) In
  other words, the principle of least privilege is that users be given
  only those privileges that are actually required to efficiently
  perform their jobs.

You just need to grant with admin option:

grant references on <schema>.<table> to <user> with admin option;

<user> can then grant references on to others, but can't pass on the admin option.


I need to run a procedure that goes through all schemas tables in MySQL server I am new in creating procedures ,Actually I need to run a procedure that will run on all schemas tables in my server when am calling the procedure it gives me this error

  ERROR 1046 (3D000): No database selected

DELIMITER $$
CREATE PROCEDURE SET_UTF_ALL_TABLES()
BEGIN

DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR(150);
DECLARE _counter INT DEFAULT 0;
DECLARE row_number INT;

DECLARE cur1 CURSOR FOR SELECT TABLE_NAME from information_schema.tables
where TABLE_SCHEMA IN ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4') ;

SELECT COUNT(*) from information_schema.tables where TABLE_SCHEMA IN
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4') INTO row_number;

OPEN cur1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

read_loop: LOOP
   FETCH cur1 INTO a;
IF done THEN
    LEAVE read_loop;
END IF;
IF _counter < row_number THEN
    ALTER TABLE a CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    SET _counter = _counter + 1;
END IF;
END LOOP;

CLOSE cur1;
END;
DELIMITER;

Selecting TABLE_NAME is not enough. You need CONCAT(TABLE_NAME, '.', TABLE_SCHEMA).
You cannot use a variable in a query as a place where tablename is stored. You must use dynamic SQL (prepared statement).

Prepared SQL Statement Syntax

  I need now to loop on each table how could I do that in prepared statement

Get each database.table which must be converted. Build proper altering query text in a variable. Prepare. Execute. Iterate next. Repeat until all tables processed. - akina


Best practices with large amount of data I'm building a solution and I have a question.

All my data is currently stored in a unique table. Each row of data is associated with a type.

My question is, what's the best practice for defining a table structure: all data in an unique table with associated type or data stored in dedicated type table?

eg: how to store people and associated colors.

all data in an unique table like id, people_id, color_id
data stored in table dedicated to color:

table blue: id, people_id
table red : id, people_id

In my opinion, it's better to have everything in a single table to avoid complex queries, but I'm scared about query time with billions of rows.

There are 3 common contemporary database designs:

1) Most database design is based on normalization, so have a look at that.

2) When that has performance problems denormalization is a common route.

3) When vast amounts of data are projected database design has moved away from these techniques (1 and 2 are two sides of the same coin) and new technologies have been used, for example have a look at noSQL.

Unless you are dealing with seriously vast amounts of data you'll probably want to start with 1) and possibly look at 2) if and when you have performance issues.

"Everything in a single table" is generally a bad idea as that table becomes huge, with a great deal of contention on it - every part of the application using it constantly. This would lead to deadlocks, for example, making queries slow.


SQL estimates are way off on DELETE statement with Triggers on huge tables I'm working with Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64)   Nov 27 2019 18:09:22   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: )

This server is on SSD drives and has a max memory of 128 gb. CostTheshold for Parallelism is 70, MaxDegree of Parallelism is 3.

I have a "Trips" table which is referenced by 23 foreign keys with the ON DELETE CASCADE option.

This table by itself is not that big (5.3 millions rows, 1.3 gb of data). But of the 23 referenced tables, two of the tables are quite big (more than 1 billions rows, 54 and 69 gb each).

The problem is when we try to delete a small amount of rows in the "Trips" table (let's say 4 rows), SQL estimates so much rows are going to be deleted, it asks for 10gb of RAM, estimates millions of rows will be returned, and locks the table. All goes to a halt and other queries block and the application time outs.

Here are the main tables and the row count for 1 delete statement:

Trips (4 rows)
Segments (27 rows, related to Trips by SegmentId)
Profiles (2012 rows, related to Segments by SegmentId)
ProfileRanges (2337 rows, related to Profiles by ProfileId)
Events (7750 rows, related to Segments by SegmentId)
EventConditions (9230 rows, related to Events by EventId)

Tables EventConditions and ProfileRanges each have more than 1 billion of rows.

Here is the plan cache : https://www.brentozar.com/pastetheplan/?id=HJNg5I0BU

When I look in SentryOne plan explorer, I can see that SQL is reading the whole table even if the "Table spool" then filters and keeps only for 2012 rows ProfileRanges and about the same for EventConditions.

When I look at the memory grant of the query with Brent Ozar's sp_blitzCache procedure, I can see that the query asks for about 10gb of RAM.

After that, the query is either waiting on SOS_SCHEDULER_YIEL (so waiting for it's turn to use the CPU after the 4ms) or MEMORY_ALLOCATION_EXT. The program times out and fails.

What can I do to make this work?

One of the thing I was thinking of, was removing the foreign keys on the two biggest table and delete their rows in an instead of trigger. But I'm not a big fan of enforcing database consistency with triggers instead of foreign keys.

Any advice or help will be appreciated

Primary Key of ProfileRanges is

ProfileId    int
ProfileRangeDefId1   int
ProfileRangeDefId2   int

Primary key of EventConditions is

EventId bigint
EventConditionDefId  int

Assuming all the related tables have correct indexing for the delete paths, you could try:

DELETE [Trips]
WHERE [ISAFileName]='ID_774199_20200311_133117.isa'
OPTION (LOOP JOIN, FAST 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

If that works, try to reduce it to the minimal number of hints.

These sorts of plans are very challenging for cardinality estimation, and the 'default' CE model often makes a mess.

Once you have a plan shape that works well, you should be able to force that shape using a plan guide etc. if necessary.


What is the proper procedure for fixing materialized view replication? Oracle 10 Standard:

I have a number of materialized views built on pre-built tables.
These are fast refreshed from a remote database over a link from one master db.
They've generally been working fine, but sometimes the view needs re-creating when a database server is bounced.

Comparing the remote (replicated) table with the master table now shows there are a few records missing in the replicated table.  Re-creating the materialized view doesn't fix this (or manually refreshing it).  There are no errors shown with the jobs that fast refresh these.

What is the procedure to fix this?  Do I need to drop & regenerate the materialized view log on the master table?

Additional information:

Ok, dropping and recreating the materialized view log did not fix this.

I suppose I can just copy the records manually, but I thought there'd be a more generic way to fix.  If complete refresh is the answer, I understand that it truncates the table first which is something I'd really like to avoid if possible.

As requested, here is the script that creates the materialized view:

CREATE MATERIALIZED VIEW AUTO_EVENT_DEFN
ON PREBUILT TABLE
USING INDEX
TABLESPACE DATA1
PCTFREE 10
INITRANS 2
STORAGE
(
  INITIAL 81920
  NEXT 1048576
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  BUFFER_POOL DEFAULT
)
REFRESH
NEXT SYSDATE + 1/(24*60)
FAST
WITH PRIMARY KEY
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS
select   aed_rid, name, description, max_check_freq_sec, max_result_freq_sec, header, footer, subject, html_format,
report_group, trigger_event, max_lines, html_base_size, ins_or_update, active, valid, defn_type, cust_req, last_modified,
modified_by, sum_cols, report_delay_secs, process_queue, sql_string, report_security, results_type, multi_loc
from auto_event_defn@kitlive;

And this is exactly what happens when you create materialized views on a prebuilt table that is already stale.

Recreating the mview the same way will not solve this, why would it? You still use the same incomplete prebuilt table.

Recreating mview logs has nothing to do with it. If you recreate mview logs now, that does not include data that was modified in the past.

Yes, the solution is complete refresh, and that does not necessarily truncate the table.

REFRESH Procedures

DBMS_MVIEW.REFRESH has a parameter called atomic_refresh which defaults to true.

A complete refresh with atomic_refresh set to true refreshes all mviews given as parameter in a single transaction. It is achieved by DELETE-ing the rows from the mview.

A complete refresh with atomic_refresh set to false refreshes all mviews given as parameter in seperate transactions. It is achieved by TRUNCATE-ing the mviews (which is much quicker than DELETE).

So specify atomic_refresh => true, and the mview will not be truncated.


Cannot find the Order Entry folder in $ORACLE_HOME/demo/schema/ I have downloaded and installed the most recent version of Oracle 12c (12.2.0.1.0). As stated on the page  https://docs.oracle.com/database/121/COMSC/installation.htm#COMSC00005,   I expected to find  oe_main.sql  in the folder $ORACLE_HOME/demo/schema/order_entry.  There is no such folder as you can see in my screenshot. I've only got the human_resources one. Why? Where are the others? Could anyone send them to me via e-mail? I am mostly interested in OE and OC . I have found some scripts on github, but I had serious problems due to many errors.

All the best!

UPDATE

Trying to execute the code  perl -p -i.bak -e 's#SUB__CWD#'$(pwd)'#g' *.sql /.sql /.dat  ,  I get the following errors:

Scalar found where operator expected at -e line 1, near "'s#__SUB__CWD__#'$(" (Missing operator before $(?)

Bareword found where operator expected at -e line 1, near "$(pwd"Missing operator before pwd?)

String found where operator expected at -e line 1, near ")'#g'" (Missing operator before '#g'? )

syntax error at -e line 1, near "'s#__SUB__CWD__#'$(" Execution of -e aborted due to compillation errors

That is 12.1 documentation. You have a 12.2 database.

Installing Sample Schemas

  Starting with Oracle Database 12c Release 2, the latest version of the
  sample schema scripts are available on GitHub at
  https://github.com/oracle/db-sample-schemas/releases/latest.

  During a complete installation of Oracle Database, the HR schema can
  be installed either manually or automatically when creating a database
  using the dbca option. All the other sample schemas must be installed
  manually via the scripts available on GitHub.

You can download the scripts from the above github URL.


Using `SELECT` as `RETURN` Argument in Function? Using Oracle, I attempted to write a function that demonstrates how to return the result of a SELECT ... query:

 CREATE OR REPLACE FUNCTION f(v1 number) RETURN number IS
     BEGIN
      IF TRUE THEN
         RETURN (select 42 from dual);
       ELSE
         RETURN v1;
       END IF;
     END;

But it does not compile.

How can I fix the above IF's RETURN to return 42, i.e. the result of select 42 from DUAL?

Save the result of your query into a variable and return the variable. For example:

CREATE OR REPLACE FUNCTION test_f(v1 number) RETURN number IS
v2 number;
 BEGIN
  IF TRUE THEN
     SELECT 42 into v2 from dual;
     RETURN v2;
   ELSE
     RETURN v1;
   END IF;
 END;

Be sure that your query always generates exactly one row. Otherwise you could get an error if no rows returned or if more than one row is returned.


Problem with View When Restoring a MySQL Backup I have a database (InnoDB) with some tables, views and routines...

Periodically I do a backup using the command line:

mysqldump -u user -ppassword --routines db_name > backup.sql

But I have a problem also using workbench to create the backup

Btw the backup process terminate without problems or warining...

After restoration I got some problems, the restore process ends well without errors, but some views are corrupted

if I try to alter it or view the structure I got an empty/blank result
if I perform a select on the view I receive the correct results (like the view is not corrupted)...

I cannot alter the view itself..

How can I fix it? And why do I have this problem?

I get a blank/empty result using MySQL Workbench 5.2.35 CE....

using plain sql (describe view_name) I got the following error:

  Error Code: 1356. View 'db.view_name' references invalid table(s) or
  column(s) or function(s) or definer/invoker of view lack rights to use
  them

But the select * from view_name still return the expected result

SHOW CREATE VIEW view_name

  Error Code: 1356. View 'view_name' references invalid table(s) or column(s) or function(s) > or definer/invoker of view lack rights to use them

SELECT * FROM INFORMATION_SCHEMA.TABLES
where table_schema = 'db_name' and table_name = 'view_name'

TABLE_CATALOG   def
TABLE_SCHEMA    db_name
TABLE_NAME      view_name
TABLE_TYPE  VIEW
ENGINE  VERSION NULL
ROW_FORMAT  NULL
TABLE_ROWS  NULL
AVG_ROW_LENGTH  NULL
DATA_LENGTH NULL
MAX_DATA_LENGTH NULL
INDEX_LENGTH    NULL
DATA_FREE   NULL
AUTO_INCREMENT  NULL
CREATE_TIME NULL
UPDATE_TIME NULL
CHECK_TIME  NULL
TABLE_COLLATION NULL
CHECKSUM    NULL
CREATE_OPTIONS  NULL
TABLE_COMMENT  'View ''db_name.view_name'' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them'

This view is a view generated by others views joined each other.

The definition of the View must be contained within the dump file.

To see the definitions, run the following command on the dumpfile:

cat backup.sql | grep -A 3 "CREATE ALGORITHM" > backup_views.sql
cat backup_views.sql

You could then drop the offending view(s) and recreate them manually.

If you have another server with the views established already, you should mysqldump the schema only. It will contain the views as well. @DTest and I wrote earlier posts on this.

Views are always processed last (after all tables) in a mysqldump.

This problem may also occur if one view references another, and the order the views are created doesn't take account of that.


How can I generate a random bytea I would like to be able to generate random bytea fields of arbitrary length (<1Gb) for populating test data.

What is the best way of doing this?

Enhancing Jack Douglas's answer to avoid the need for PL/PgSQL looping and bytea concatenation, you can use:

CREATE OR REPLACE FUNCTION random_bytea(bytea_length integer)
RETURNS bytea AS $body$
    SELECT decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,''), 'hex')
    FROM generate_series(1, $1);
$body$
LANGUAGE 'sql'
VOLATILE
SET search_path = 'pg_catalog';

It's a simple SQL function that's cheaper to call than PL/PgSQL.

The difference in performance due to the changed aggregation method is immense for larger bytea values. Though the original function is actually up to 3x faster for sizes < 50 bytes, this one scales much better for larger values.

Or use a C extension function:

I've implemented a random bytea generator as a simple C extension function. It's in my scrapcode repository on GitHub. See the README there.

It nukes the performance of the above SQL version:

regress=# \a
regress=# \o /dev/null
regress=# \timing on
regress=# select random_bytea(2000000);
Time: 895.972 ms
regress=# drop function random_bytea(integer);
regress=# create extension random_bytea;
regress=# select random_bytea(2000000);
Time: 24.126 ms


Oracle_Loader Quote in quoted field I have a create table ext_file (....) type oracle_loader query that opens a text file to pull data out of it. The access parameters are:

records delimited by newline
fields terminated by '\t'
optionally enclosed by '\"'
ltrim
missing fields are null

The problem I just noticed is that one of the fields is height and it is stored in the text file as:

"5'6.35""

The use of a " inside a "-enclosed field without an escape is causing every record with height to be kicked out.

How can I tell oracle_loader to accept these values (and I really could care less if the " makes it in or if the field is trimmed to 5'6.35).

I have two solutions, neither of which are optimal:
1. Use sed to escape the " inside the " as \"
2. Omit the optionally enclosed by '\"' line, which will break lines in which the field terminator is inside a field

The solution is to move the enclosed by clause into each line instead of overall for the table. For example:

create table ext_tb (
    a varchar2(255),
    b varchar2(255),
    height varchar2(255)
) organization external (
    type oracle_loader
    default directory my_data_dir
    access parameters (
        records delimited by newline
        fields terminated by '\t'
        -- DO NOT add the enclosed line here
        lrtrim
        missing field values are null
        (
            a char optionally enclosed by '"',
            b char optionally enclosed by '"',
            height
        )
    ) location('my_file.ttx')
)

Now, that pesky height field will be in the database as "5'6.35"" (three double-quotes in it). You can run a query on the column to remove the quotes.


One MySQL database or many SQL Server databases At the moment I have an in-house Access database, data stored by our online survey provider, and data stored by our project management provider, both in databases. What I want is an in-house database where I have all data harmonized. Now I see two options for us (since we are money limited)

Set up a MySQL-DB with all data in one database or
Set up many SQL Server Express databases

I have worked with a SQL Server database but I have not the experience to decide what is better and why. So it would be great if someone could help me to decide that and tell me why.

If hosting externally is an option, you can get a MSSQL Database on Azure for as little as $5/MO.  http://azure.microsoft.com/en-us/pricing/details/sql-database/

The Basic tier has a 2GB limit, but you can get 250 GB for $15/MO.

Azure plans also come with:

  built-in backup support, with the ability to point-in-time restore
  your data from any transaction up to 35 days. Additionally you can
  automatically replicate to another Azure region of your choice. This
  enables you to easily implement a disaster recovery policy designed
  for your business needs.

Might be worth checking out for just a few $$ per month.


Error granting access to sequence with PostgreSQL 8.4 I'm attempting to run this query

GRANT USAGE ON [db].users_uid_seq TO [user];

db and user are redacted (obviously).

I get this error:

ERROR:  syntax error at or near "GRANT"

I've tried a number of permutations of users, including / not including the db prefix, slightly different syntax, etc. per other stack exchange postings with no luck.

I'm using PG 8.4 with namecheap's shared hosting plan. It looks like this wouldn't be an issue with a newer version of pg but I don't have that option right now.

Any suggestions are much appreciated.

The syntax for GRANT in PostgreSQL 8.4 is

GRANT { { USAGE | SELECT | UPDATE }
    [,...] | ALL [ PRIVILEGES ] }
    ON SEQUENCE sequencename [, ...]
    TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]

You can't just exclude SEQUENCE. If you could it'd be in [], without which it's not optional.

GRANT USAGE ON SEQUENCE users_uid_seq TO myUser;

As a side note, PostgreSQL 8.4 was EOL on July 2014


How to keep an Oracle 11g instance lean? As a Mac user I run a local Oracle Enterprise Linux (OEL) Developer Day VM that houses an Oracle 11g instance for development/unit testing.  I frequently refresh the data in the schemas (sometimes multiple times a day) and do not have a need for snapshots or backups.  The number of rows in each schema's tables are orders of magnitude smaller than those of a production instance.

I've had to extend the tablespaces' size more than a few times and I suspect I have improper resource allocation to begin with (SGA, PGA) for the way in which I'm using the instance.  I have a similar concern about my sizing/configuration of the VM itself since I'm only using it to house the instance, not for SQL Developer, TimesTen, etc..

I'm looking for recommendations/resources to help me

Optimize my database instance for a small amount of data in the first place
'Clean' my database instance so that I don't need to increase data file size
Modify my OEL VM to most efficiently run the only thing I
need it to run -- the Oracle instance

For example, I have turned off snapshot collection.  But I think there are other (more?) impactful changes I can make to help increase the performance and reduce the footprint of VM.

The best check for memory allocations is the buffer advisories. These estimate the impact on caching effectiveness of modifying the cache sizes, so you know whether you have the sizes of the PGA and SGA correct, and whether reallocating memory from one to another would be beneficial.


Why does jsonb use more space for storing integers? I did some small tests to try these data types, found some puzzling results.

Summary:

jsonb uses more space for integers than strings:

select pg_column_size('{"a":"1"}'::jsonb) as json_size

18

select pg_column_size('{"a":1}'::jsonb) as json_size

28

Why does the size of jsonb inflate when using integers?

Long version:

I was originally interested in using an array of integers, here are those tests. In each pair of comma separated numbers, first corresponds to json_size (bytes), second number is array_size (bytes).

select pg_column_size('[]'::jsonb) as json_size,
       pg_column_size(array[]::smallint[]) as array_size

8,16

select pg_column_size('[1]'::jsonb) as json_size,
       pg_column_size(array[1]::smallint[]) as array_size

20,26

Check if jsonb actually stores integers as plain text; if so then this next one should use 4 bytes more for jsonb:

select pg_column_size('[12345]'::jsonb) as json_size,
       pg_column_size(array[12345]::smallint[]) as array_size

22,26

Strange, it uses 2 bytes more, no idea what that means. Try some more practical and realistic data now:

select pg_column_size('[123,234,345,456,567,678,789,890]'::jsonb) as json_size,
       pg_column_size(array[123,234,345,456,567,678,789,890]::smallint[]) as array_size

104,40

What happened to jsonb here? What caused it to inflate? Maybe if I use smaller numbers that have less characters...

select pg_column_size('[1,2,3,4,5,6,7,8]'::jsonb) as json_size,
       pg_column_size(array[1,2,3,4,5,6,7,8]::smallint[]) as array_size

104,40

Nope. What about strings?

select pg_column_size('["1","2","3","4","5","6","7","8"]'::jsonb) as json_size,
       pg_column_size(array[1,2,3,4,5,6,7,8]::smallint[]) as array_size

48,40

It seems that jsonb likes strings and not integers.

I quickly made it a rule of thumb that I should only use strings in jsonb and use native integers or arrays for numeric data, but I don't know if this rule is misguided or why this anomaly occurs.

It's simple. JSON doesn't have a concept of an integer, only a "number". From the docs

  When converting textual JSON input into jsonb, the primitive types described by RFC 7159 are effectively mapped onto native PostgreSQL types, as shown in Table 8-23.

That table shows a JSON "number" is mapped to a numeric. The max precision on numeric is

  up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

However, I don't think it actually stores that. It does store a numeric of some sort. The numeric source is quite frankly kind of difficult to follow.

Using an array of smallint is clearly better if it fits your workload. It has less wasted space for packing, and less wasted space for the data itself. smallint[] however

Does not support the same range of numbers.
Does not support embedding different types.
Does not support any precision at all.
Does not exchange well with web-clients.


Can you create a clustered DB in Azure using Windows 2003 and SQL Server 2005? We need to replicate an environment in Azure, running a clustered DB. The current physical environment is running on Windows 2003 and MS SQL Server 2005 R2.

We know that there's no OS/DB support but that it's possible to install these versions are run there.

However, how do you do the clustering side? There's no 'shared disk' as such. I believe in newer versions you use something called SIOS datakeeper that manages replication for you (for example, in a SAP set up: https://docs.microsoft.com/en-us/azure/virtual-machines/workloads/sap/high-availability-guide). Can you use this on Windows 2003, with SQL 2005, in Azure?

Thanks
Ross

Shared Disks are currently in preview. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/disks-shared

But if you have no choice but to run old, unsupported software, you shouldn't attempt anything as complicated to implement and operate as cluster.  Just install the software on a single VM with premium disks, and live with the 99.9% uptime SLA for a single Azure VM.  And with a single stand-alone VM, you may have an easier time supporting a more current OS or SQL Server version.

Put another way, a failover cluster on with Windows Server 2003 and SQL Server 2005 will definitely be more expensive, and will probably have worse uptime than a single standalone VM.  With a cluster you have to properly configure and be able to troubleshoot the quorum model, the Azure load balancer that supports the floating IP, and you must have a domain with highly-available domain controllers.   The likelihood that you get all this right, and have the ability to troubleshoot and repair it if it gets messed up is just too low.  And if you can't figure something out, you won't be able to get help.


How does the alter command work internally when adding a column? Can anyone explain what the internal operations are that takes place when an alter command is issued? How is column modified? Does it use created_tmp_tables or created_tmp_files?

As per my test, I created a table and with the alter command I added a column. After checking the created_tmp_file status variable, it increased by 3.

Can someone explain how this alter command works internally?

See the ALTER TABLE docs here:

http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

"In most cases, ALTER TABLE makes a temporary copy of the original table."


MySQL: How to sync on demand beetwen two database in the same server I'll like to know if it's possible to have a systeme on synchronisation beetween two database in the same server.
I have a production database in my server , i want a dev database (same database with another name)
And i want to sync on demand.
Is it possible ?

As u told it possible to create another database(with different name) with in the server. try once in local db then you can go into production.


Are the cost percentages in this SQL Server plan over 100% for a valid reason? I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet:

Why are many of the costs listed above 100% ?  Shouldn't that be impossible?

The visual cost estimator is crap. This sort of stuff happens all the time.  Just go with the highest ones are the most expensive and attack those ones first.


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

Certainly:

SHOW hba_file;
SHOW ident_file;

or just

SHOW ALL;

:-)


Multi-victim deadlock in SQL Server I have been seeing a lot of deadlocks occurring on a system with deadlock graphs similar to this one:

I have been advised that it is a multi-victim deadlock but I cannot re-create a deadlock graph such as this one. Also, why does it only show one victim?

Any help or advice would be most appreciated.

Andrew

I suppose this graph is coming out of SQL Profiler. Profiler behaves this way. it cannot display multi-victim deadlocks correctly. If you pick the very same deadlock from the system_health extended events session (no extra trace necessary as this one is running by default), you can get the correct visual within Extended Events Viewer.
Also you can use this link to get a free deadlock collector and parser which also gets that information and the graphs: https://sqldeadlockcollector.codeplex.com/

as requested: yes, I am the person who created the deadlock collector which is free for anyone to download&use


Can't recycle the ERRORLOG - error 17049 When we attempted to run sp_cycle_errorlog we get the following:

  Msg 17049, Level 16, State 1, Procedure sp_cycle_errorlog, Line 9
  Unable to cycle error log file from
  'G:\SQL_DATA1\MSSQL10_50.ABC\MSSQL\Log\ERRORLOG.15' to
  'G:\SQL_DATA1\MSSQL10_50.ABC\MSSQL\Log\ERRORLOG.16' due to OS error
  '1392(failed to retrieve text for this error. Reason: 15105)'. A
  process outside of SQL Server may be preventing SQL Server from
  reading the files. As a result, errorlog entries may be lost and it
  may not be possible to view some SQL Server errorlogs. Make sure no
  other processes have locked the file with write-only access."
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So far we have tried:

Failing over the SQL cluster resource to second node, rebooting server, failing back and rebooting second node
Using Process Explorer tool - this identified the only process using the ERRORLOG file to be sqlservr.exe

SQL version details:

  Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)  Aug 19 2014
  12:21:34  Copyright (c) Microsoft Corporation Enterprise Edition
  (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

There are two SQL instances on this two node SQL cluster, on the other instance the log cycles over with no issue.

I have seen a couple of posts on this subject but both appear to have been resolved by killing the non-sqlserver process. I'm presuming that the sqlsrvr.exe process is expected to be locking the file?

Any help appreciated.

From System Error Codes (1300-1699): OS error 1392 indicates file corruption.

  ERROR_FILE_CORRUPT
  1392 (0x570)
  The file or directory is corrupted and unreadable.

So it looks like you have corruption on your G: drive.

I would start with a chkdsk of that drive and all other drives.

Since corruption usually doesn't fix itself and can have the tendency to worsen, personally I would fail over to the other node ASAP before other files (I don't know what else lives on the drive) corrupt and the situation gets trickier and trickier.


In the practice of Kimball's Dimensional data warehouse design model, do lookup tables (not dimensions) contain a sid column? Trying to create a simple lookup table for a drop down on a BI web page.
The table contains 2 columns, an key and a description.
What is the best practice with regard to Dimensional Modeling when designing this table?
Would key => number, description => character (column => data type) be sufficient or does the key need to be a SID?

Think of a dropdown in a web page or web form like "Country".

If your natural key can only ever be associated with one description, and your lookup table only contains static reference data, then I see no need for a surrogate key.

In the example you gave, your drop down list would contain countries. From my perspective, if the dimensional model is based upon the star-schema then country would not become an independent dimension, but would instead be an attribute on another dimension (such as geography or customer etc). This would not require the surrogate key to be included even if you were to add one and therefore would be a waste of time, effort and storage.

If your dimensional model is based upon the snowflake or your key can be associated with more than one description (for example if the description can change over time) then yes I would include a surrogate key. I would not put this on the reference table though, the surrogate key should belong to the dimension that owns the data.


How to Insert into with postgresql and JSONB I am using Postgresql 9.5

I have two tables(product and product_temp) with jsonb colums

I would like to insert the values from  product_temp to product

but the name of the data inside the json is not equal
ex:

INSERT INTO product(data1->>'__COD__', data1->>'__PAGE__', job_id)
    SELECT data2->>'cod', data2->>'page', job_id
         FROM product_temp where  someid = '123'

Quickly did lots of testing and i got what you wanted.

INSERT INTO product(data1, job_id)
SELECT json_build_object('__COD__',data2->>'cod','__PAGE__',data2->>'page') data1, job_id FROM product_temp;

Your data1 is one column. Tested with PostgreSQL 9.5


Dump MySQL table to csv file (utf8mb4_unicode_ci) I have MySQL table with columns (character-set: utf8, collation: utf8mb4_unicode_ci) to be exported to a .csv file.

mysqldump -u *** -p -t -T/tmp/ dbname tablename --default-character-set=utf8mb4

The output has more lines than the records in the table due to some symbols? characters? to break the lines incorrectly. (see pics below) Adding "enclosed by" or "escaped by" do not help.

The CSV "de facto standard" allows for fields to contain line breaks. So, what you get is the result of some of your data in your original table containing line breaks.

When that's the case, one record uses more than one row of text.

Some libraries or some programs can handle and interpret that adequately, but not all do. For instance, you can use MS Access, and it will work; but if you try MS Excel, line breaks within a field won't be properly handled. If you work with MySQL or PostgreSQL, they can import/export CSV with line breaks in fields.

Check, for instance, Comma-separated values at WikiPedia or RFC 4180

  Fields with embedded line breaks must be quoted (however, many CSV implementations do not support embedded line breaks).


What clustering features are required to implement Availability Groups? My understanding is that if one wants to implement Always On / Availability Groups (without FCI) the Windows Clustering Service would need to be installed, i.e. a Windows Cluster is required but not a SQL Cluster. Is that correct?

MSDN has an excellent article about this exact topic.

Some salient points:

  Ensure that each computer is a node in a Windows Server Failover Clustering (WSFC) cluster.

SQL Server Clustering can be used, but is not required.

From this MSDN page, we get:

  AlwaysOn Availability Groups, the high availability and disaster recovery solution introduced in SQL Server 2012, requires Windows Server Failover Clustering (WSFC). Also, though AlwaysOn Availability Groups is not dependent upon SQL Server Failover Clustering, you can use a failover clustering instance (FCI) to host an availability replica for an availability group.


MySQL: Why am I getting a syntax error when using the FIELDS ESCAPED BY clause on a select statement I am attempting to generate a csv file including headers from a table in my MySQL 8.0 database.  After a lot of searching I found the following suggest syntax:
    SELECT "ansId","assmt","ques","answerCode","details"
    FROM ayanswer
    UNION
    SELECT ansId,assmt,ques,answerCode,details
    FROM ayanswer
    INTO OUTFILE 'c:/data/test1.csv'
    FIELDS TERMINATED by ','
    OPTIONALLY ENCLOSED BY '"'
    FIELDS ESCAPED BY '\\';

However when I run this statement in mysql I get the following error:
ERROR 1064 (42000) at line 1: 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 'FIELDS ESCAPED BY '\'' at line 9
According to the MySQL Server 8.0 reference manual this syntax should be correct.  So what am I doing wrong?

This seems to be a simple syntax issue. Use the below query instead which basically doesn't use FIELDS before ESCAPED by '\\' since it's already specified before TERMINATED by ','.
 SELECT "ansId","assmt","ques","answerCode","details" FROM ayanswer
 UNION
 SELECT ansId,assmt,ques,answerCode,details FROM ayanswer
 INTO OUTFILE 'c:/data/test1.csv'
 FIELDS TERMINATED by ','
 OPTIONALLY ENCLOSED BY '"'
 ESCAPED by '\\';

Supporting Resources

13.2.10.1 SELECT ... INTO Statement

TABLE employees ORDER BY lname LIMIT 1000
    INTO OUTFILE '/tmp/employee_data_1.txt'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY '\'
    LINES TERMINATED BY '\n';

13.2.7 LOAD DATA Statement


Table scan estimates when there are no stats on the table Suppose I create a table with 1000 rows but don't gather statistics on the table or run a query that should trigger an automatic statistics creation:

create table test( a int )
go
insert into test select 1
go 1000
select * from test
sp_helpstats 'test','all'

When I check the plan for the select, I can see that the estimate is accurate and it's 1000.  Where does that estimate comes from?  Does it have anything to do with sys.partitions rows column?

Try a different question: should I need to create statistics just to see how much space a table uses?

EXEC sp_spaceused 'test';

That query shouldn't just fail, right? Yet it provides the correct row count of 1000 rows. You can look at the code of sp_spaceused quite easily:

SELECT
            @reservedpages = SUM (reserved_page_count),
            @usedpages = SUM (used_page_count),
            @pages = SUM (
                CASE
                    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                    ELSE 0
                END
                ),
            @rowCount = SUM (
                CASE
                    WHEN (index_id < 2) THEN row_count
                    ELSE 0
                END
                )
        FROM sys.dm_db_partition_stats
        WHERE object_id = @id;

This doesn't mean that the estimate "comes from the sys.dm_db_partition_stats DMV". DMVs are created for end users to write application code against. The approximate row count of the table is stored in some internal structure which can be used to provide a cardinality estimate when there's no other option available for the query optimizer. If you need to know the details about this internal structure you could try using a debugger, but it's not like there's any guarantee that it wouldn't change between SQL Server versions.

It's worth mentioning that you should almost never have tables without statistics in production.


Does a unique non-clustered index help with locks? I was wondering if SQL Server behaves differently with a unique non-clustered index versus a non-unique clustered index when it comes to KEY locks.

Theoretically yes, practically no.

If SQL Server can guarantee that a field is unique, then it can build execution plans differently. Rob Farley blogged an example with AdventureWorks where the unique index isn't even shown in the execution plan, and yet its existence let SQL Server build a better plan.

If your data really does qualify for a unique index, then specifying it as unique will ensure that SQL Server always estimates 1 row to come back when you search for one value. That'll mean you're more likely to get more granular locks instead of table locks.

However, if you're worried about lock escalation, it's probably because you're not searching for equality. You're joining to other tables, or using range searches. In those cases, the uniqueness of an index isn't going to save you.


Tempdb data file disappearing I recently moved my Temp database files onto a separate drive (I had two data files and one log file).

However, after restarting the server, my second data file disappeared both from the file list on the gui and from sp_helpfile. I've managed to reproduce this behaviour several times: I add a new data file, see it appear both in the gui and sp_helpfile, and disappear after rebooting the server.

I've run dbcc checkdb against the database and it returns no errors. There's no information either in the SQL logs or the windows application event log.

After doing a little more research, I ran the following query:

select d.name, m.* from sys.master_files m
inner join sys.databases d
on (m.database_id = d.database_id)
where d.name like 'tempdb'
order by 1, 2

Results: http://pastebin.com/Zu5fJ2hU

It shows four different secondary data files, none of which appear in sp_helpfile. If I try to use any of those names SQL server doesn't allow me to. None of these files physically exist on the disk. My server is SQL 2012 Standard.

(Consolidating the comments into an answer, so it's more easily consumable by searchers.)

It appears this is related to not granting the appropriate permissions to the data container when moving the location of the physical files. Without having tried it, I believe this would also apply to user databases, not just tempdb.

I have an old, but still technically accurate, video on how to move all the system databases (not just tempdb) here, which includes showing the process of assigning permissions to the new data container. The database engine requires Full Control on This Folder, Subfolders, and Files starting at the data container root.

After the permissions are set correctly on the data container, the dangling files should be removable from the system catalogs using ALTER DATABASE ... REMOVE FILE ....


Are "create user" and "grant connect" functionally equivalent? I don't have a problem per se, but I've noticed that if I grant a database permission to a principal that doesn't exist in the database, the permission shows up in sys.database_permissions but that the user cannot connect to the database (as expected). If I then grant connect to the user, everything is fine. Which made me wonder if create user and grant connect were functionally equivalent. That is, is there anything additional that create user does that grant connect doesn't?

You can't grant database permissions to server principals: you need to create a database principal first, which is what you probably have already done.

What you are referring to is the lack of CONNECT privileges to the GUEST principal, which was likely revoked by a wise DBA. When CONNECT privileges is not granted explicitly to a database principal, it inherits the permission from the guest user.

Here is a repro script that can help you understand how things work:

-- create a test database
CREATE DATABASE testPermissions;
GO

USE testPermissions;
GO

-- revoke CONNECT permissions from users that
-- are not granted CONNECT explicitly or
-- do not inherit the permission from server
-- or database roles.
-- this is considered a security best practice
REVOKE CONNECT FROM GUEST;
GO

-- create a test table
CREATE TABLE someTable (
    someColumn int
);
GO

-- insert some values
INSERT INTO someTable VALUES (1);
GO

-- create a login, AKA server principal
-- this login has NO PERMISSIONS on the database
CREATE LOGIN testlogin WITH PASSWORD=N'aVeryLongPasswordNobodyWillEverGuess', CHECK_POLICY = OFF
GO

-- if you try to grant database permissions
-- to a login you get an error
GRANT SELECT TO testlogin;
GO

-- Msg 15151, Level 16, State 1, Line 1
-- Cannot find the user 'testlogin', because it does not exist or you do not have permission.

-- if you create a database user you are creating
-- a link between a database principal and
-- a server principal with the "FOR LOGIN" clause
CREATE USER testUser FOR LOGIN testLogin;
GO

-- now if you grant some permissions to the
-- database principal you won't get any errors
GRANT SELECT TO testUser;
GO

-- you can now see that the database principal
-- has been granted some permissions
SELECT pe.class_desc
    ,OBJECT_NAME(pe.major_id) AS target_object_name
    ,pe.permission_name
    ,pr.name AS grantee
    ,pr.type_desc
FROM sys.database_permissions AS pe
LEFT JOIN sys.database_principals AS pr
    ON pe.grantee_principal_id = pr.principal_id
WHERE pr.name = 'testUser';
GO

-- class_desc  target_object_name  permission_name  grantee   type_desc
-- ----------- ------------------- ---------------- --------- ----------
-- DATABASE    NULL                SELECT           testUser  SQL_USER

-- If you try to connect as the "testUser" database principal
-- you will get an error, as it doesn't have CONNECT privileges
EXECUTE AS USER = 'testUser';
GO

-- Msg 916, Level 14, State 1, Line 1
-- The server principal "testlogin" is not able to access the database "testPermissions" under the current security context.

-- Now grant the CONNECT privilege
GRANT CONNECT TO testUser;
GO

-- If you try to connect as testUser things
-- will now work as expected
EXECUTE AS USER = 'testUser';
GO

USE testPermissions
GO

-- the select permissions are already granted:
-- the query works
SELECT *
FROM someTable;

REVERT;
GO


Running pt-table-checksum with replicate-do-db option on I've read the documentation on percona page, but still a bit confused, especially about the --replicate option.

My setting is that the slave is only replicating certain databases from master, say db1 and db2. So I have this following line in the my.cnf file on slave host:

replicate-do-db=db1
replicate-do-db=db2

So when I use the tool, I know I need to pass --databases=db1, db2 as an option, but do I need to write the resulting checksum table into one of these dbs with --replicate=db1.checksum? I know by default it creates a database called percona and writes to percona.checksum, but since I'm not replicating all the databases, will it also create and write such a db on slave?

And after using the tool, should I be running USE db1 before dropping the checksum table on master so that this is logged and sent to slave?

I know this can be easily tested but when I read the documentation mentioning of causing replication failure scared me. I somehow screwed my local MySQL and only having access to this two servers. Appreciate your help.

But do I need to write the resulting checksum table into one of these dbs with --replicate=db1.checksum?

Yes, you need. Otherwise it would not be replicated.

  And after using the tool, should I be running USE db1 before dropping the checksum table on master so that this is logged and sent to slave?

Yes.


Postgres Streaming Replication: How to re-sync data in master (with standby) after failover I am absolutely new to postgres 9.1 replication mechanisms.

I have managed to set up streaming replication between a master and standby postgres (Both have x number of records). At one point when the master server fails, using the trigger file mechanism the standby takes over, and accumulates additional data (say now has x+y number of records).

Now when the Original Master Server comes up, it still has x number of records (which is now the new master). Is there a way to fetch only the delta, i.e. newly added 'y' number of records from the standby and restart as Master.

Or do I have to always take entire base backup?

During the process there is a good chance that the process fails requesting wall files that have never existed in the old time line. That does not help quickly restoring the requested redundancy.
The safest method is to just create a fresh new replica.
So if your requirement is to always have at least one valid replica, make sure you have two of them. This gives you time to rebuild the former primary as a new replica and still keep operating at your requested redundancy level.
There was a bug that is solved in 9.5.22 that caused premature recycling of wall files during crash recovery.


"Unexpected token" in a DB2 SQL statement that runs OK in Postgres I trying execute statement like following (i obfuscated it a little) in DB2:

UPDATE FIRSTTABLE SET fstatus=1 WHERE fid in (SELECT T.fid
  FROM FIRSTTABLE T
  INNER JOIN SECONDTABLE V ON V.efvid = T.efvid
  INNER JOIN OTHERTABLE E ON E.efid = V.efid
WHERE (efname = 'event')  AND  (eftname = 'eventhtml') FOR READ ONLY)

And receiving error:

42601][-104] An unexpected token "UPDATE FIRSTTABLE SET fstatus" was found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<query_into_expr_top>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.7.85

But, with, for example, postgresql this script works fine.
P.S. I am using DB2 Express, if it matters.

The problem is in the FOR READ ONLY statement. DB2 implicitly add's the same effect for all nested query's, and FOR READ ONLY makes it ambiguous. However, i dunno why i getting so strange error message.


Combine individual sensor values into an overall value with correction factors For a school project, we are trying to calculate (corrected) Ozone values based on a combination of 5 sensors (3x O3, 1x temp, 1x humidity).

We are using MySQL and PHP for the rest of the project.

The table Measurements has this structure:

id (int(11))
time (datetime)
value (float)
measured_value (float)
sensor (tinytext)
unit (tinytext)
measurement_short_type (tinytext)
stream_id int(11)

so an example row looks like this:

ID    Time                 value  measured_value  sensor  unit   measurement_short_type  stream_id
----  -------------------  -----  --------------  ------  -----  ----------------------  ---------
3324  2016-05-21 11:00:34  0      193             O3r     KOhms  O3                      6511

As you can see, we have 2 columns, value (float) and measured_value (float).

In order to calculate the correct, final value based on the individual sensor data (stored in measured_value), we need to apply a formula similar to this for every datapoint:

Corrected value[datetime] = ("6511".measured_value[datetime] * -0.106830613)
                          + ("6512".measured_value[datetime] * 0.065201457)
                          + ("6513".measured_value[datetime] * 0.335456161)
                          + ("6514".measured_value[datetime] * -0.248569888)
                          + ("6515".measured_value[datetime] * 1.423460009) + -2.113591555

where 6511, 6512, ..., 6515 are the IDs for the 5 sensors (3x O3, 1x temp, 1x humidity). The values for all sensors are stored in the same table.

The notation "6514". measured_value[datetime] means: use the measured_value (not value!) of the row that has approximately the same datetime stamp, and the stream ID 6514.

All sensors report their value approximately at the same time, every 30 minutes (i.e. x:00 and x:30), but not exactly: one sensor could have 10:59:59 as timestamp, another 11:00:34.

As far as I know, there are 2 ingredients to solving this:

How can we apply the correction per the formula above in MySQL to get an exact value, based on the 5 sensor input values that are generated roughly every 30 minutes?
Since the timestamps for the 5 individual sensors are not always exactly the same, is there a way to have a time "fudge factor", since the corresponding sensor input values are never reported at exactly the same timestamp?

If this is not possible, can somebody advise on how to achieve this? We cannot influence the timing of the sensors, but we could change the DB/table structure if that facilitates the calculation.

You could do the following, provided that your sensor 6511 takes measurements reliably every 30 minutes (use instead the one that is most reliable). This will calculate measurements based on the recordings of that primary sensor used (in the main FROM, in this case 6511), show the exact date and time of such recording, also show an adjusted time in 30 min slots, and use any available measurements for all other sensors, whichever latest available up to 10 minutes after the primary measurement:

SELECT "date",
       DATE("date") + INTERVAL round((UNIX_TIMESTAMP("date") - UNIX_TIMESTAMP(DATE("date")))/1800) * 30 MINUTE AS adj_date,
       (measured_value * -0.106830613) +
       (SELECT measured_value * 0.065201457
        FROM Measurements
        WHERE stream_id = '6512' AND "date" < (s1."date" + INTERVAL 10 MINUTE)
        ORDER BY "date" DESC LIMIT 1) +
       (SELECT measured_value * 0.335456161
        FROM Measurements
        WHERE stream_id = '6513' AND "date" < (s1."date" + INTERVAL 10 MINUTE)
        ORDER BY "date" DESC LIMIT 1) +
       (SELECT measured_value * -0.248569888
        FROM Measurements
        WHERE stream_id = '6514' AND "date" < (s1."date" + INTERVAL 10 MINUTE)
        ORDER BY "date" DESC LIMIT 1) +
       (SELECT measured_value * 1.423460009
        FROM Measurements
        WHERE stream_id = '6515' AND "date" < (s1."date" + INTERVAL 10 MINUTE)
        ORDER BY "date" DESC LIMIT 1) + -2.113591555 AS "Corrected value"
FROM "Measurements" AS s1
WHERE stream_id = '6511';

Ensure you have an index by stream_id, "date"!


Get the id of multiple inserted/updated rows I insert my _as_ci accent sensitive, case-insensitive rows. Tail is unique, id is auto_increment.

INSERT INTO tails (tail) VALUES ('Hello'), ('cafe'), ('Café') ON DUPLICATE KEY UPDATE tail = VALUES(tail);

I could try to get the ids out like this:

SELECT id, tail FROM tails WHERE tail IN ('Hello', 'cafe', 'Café')

But I've had experience with it matching rows in MySQL but I can't do the exact matching process in PHP. If only PHP could collation compare.

Is there any way to get MySQL to also return the original string from the IN so I can do an exact comparison in PHP or is there a better method I'm missing?

This code is run extremely often, so I can't afford to run each insert individually.

I could try to get the ids out like this:

  SELECT id, tail FROM tails WHERE tail IN ('Hello', 'cafe', 'Café')

Do

SELECT tails.id, tails.tail, criteria.pos
FROM tails
JOIN ( SELECT 1 pos, 'Hello' tail UNION ALL
       SELECT 2,     'cafe'       UNION ALL
       SELECT 3,     'Café' ) criteria ON tails.tail = criteria.tail

and no problems with identifying (you know what pos to what tail was assigned).


PostgreSQL won't take new timezone In Venezuela a timezone change have been undertaken on May 1st 2016. The old offset for the Venezuela timezone was -04:30 and the new one is -04:00.

All database servers have been updated with the new timezones, when I run this command at the Linux prompt I get the expected output:

$ date +'%:z %Z'
-04:00 VET

Most PostgreSQL clusters happily took the new timezone after bouncing them:

Good behaved cluster:

postgres=# show timezone;
TimeZone
-----------
localtime
(1 row)

postgres=# select now();
      now
-------------------------------
2016-05-02 10:08:54.532902-04
(1 row)

postgres=# SELECT utc_offset FROM pg_timezone_names WHERE name = 'America/Caracas';
utc_offset
------------
-04:00:00
(1 row)

In the case of two particular database servers, the clusters refuse to take the new timezone offset no matter how many times we bounce the cluster.

Ill-behaved cluster 1:

postgres=# SELECT utc_offset FROM pg_timezone_names WHERE name = 'America/Caracas';
utc_offset
------------
-04:30:00
(1 row)

postgres=# select now();
       now
----------------------------------
2016-05-02 09:39:03.678385-04:30
(1 row)

postgres=# show timezone;
TimeZone
-----------------
America/Caracas
(1 row)

The servers who took the change as well as the ones which didn't, all have the default value for the timezone parameter in postgresql.conf.

I need advice on how to solve this, since all other clusters in other servers took the change without a hassle.

What else can I check?
What am I missing?

One of the clusters that failed to take the new timezone is 8.2 and the other is 9.3,

To confuse it even more, one of the ill-behaving clusters (the 9.3 one) has the correct offset from the pg_timezone_names view but returns the time from the old offset:

Ill-behaved cluster 2:

postgres=# SELECT utc_offset FROM pg_timezone_names WHERE name = 'America/Caracas';
 utc_offset
------------
 -04:00:00
(1 fila)

postgres=# select current_time;
        timetz
-----------------------
 10:20:16.398867-04:30
(1 fila)

postgres=# show timezone;
 TimeZone
-----------
 localtime
(1 fila)

I'm totally lost here. Any light on this issued would be most appreciated.

PostgreSQL can be built to either use the operating system's time zone database or its own.  Check with the command pg_config --configure whether the option --with-system-tzdata was used.  If not, it is using its own time zone data, and that hasn't been updated yet.  If so, to fix, either wait for the next releases, or install the packages the way you have installed it on the other hosts.


Secondary replica database file location in Availability Group I inherit a setup of two nodes (primary & secondary replica) Availability Group.
On my secondary replica node, the SQL server instance default files are stored at:

My default primary replica node  files are as follow:

When I look at the synchronizing database's properties on my secondary replica, I see its database files as follow

They are primary replica node directories.  Is this how it works in Availability Group?
That is when you look at the file group of your secondary replica database, they show the paths of the primary replica database even though the data file and log file are stored at a different local directory?

If you try and open the database properties window on a secondary replica, SSMS will throw you an error, so my guess is that you're not actually connected to the secondary replia when you're checking these paths.
Run the following on both nodes:
select db_name(database_id), name, physical_name
from sys.master_files

This will show you the physical paths that SQL Server is using to access each file, and I would expect will show you different paths on each replica.


SQL Server Resource governor user-defined resource pools In the context of the SQL Server resource governor, why would I create a user-defined resource pool? It seems like you could get almost the same result by using the default resource pool for everything, and controlling resources instead using the workload group settings.

I've heard that there can be issues if too many resource pools are created, so I'm wondering what the advantages are over the workload group settings?

Think of the resource pool as physically dividing the CPU, memory, and IO of the server. This can be useful when you need to limit resources used by overlapping workloads. The workload groups further limit resources used at a query level within a pool. This can be useful when you need to limit resources used within a workload, perhaps to improve throughput.

The resource governor overview provides a few use cases that can only be satisfied with resource pools as opposed to resource groups. For example:

  Throttle IO resources for operations such as DBCC CHECKDB that can saturate the IO subsystem and negatively impact other workloads.

Resource pools have the MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME options to limit IO. Workload groups do not have any options to limit IO.

Workload groups are also limited in how they can control CPU usage. The REQUEST_MAX_CPU_TIME_SEC option doesn't actually enforce a hard limit on CPU by query:

  Resource Governor will not prevent a request from continuing if the maximum time is exceeded. However, an event will be generated. For more information, see CPU Threshold Exceeded Event Class.

To properly limit CPU through a workload group you would need to use the MAXDOP option for workload groups but that may not be granular enough. Resource pools offer much better options for preventing runaway queries from using all of the CPU available on a server.

It may seem that workload groups could be used to balance query memory grants for competing workloads with the GROUP_MAX_REQUESTS and REQUEST_MAX_MEMORY_GRANT_PERCENT options. However, consider a workload that sends thousands of queries of various sizes to run against the server with a concurrency of up to 40 queries at once. A small percentage of the queries are memory hogs and if left unchecked would use all available memory for query grants. You find through performance testing that limiting REQUEST_MAX_MEMORY_GRANT_PERCENT to 4% gives reasonable overall throughput. If you only configured this through a workload group you'd end up with:

GROUP_MAX_REQUESTS = 40
REQUEST_MAX_MEMORY_GRANT_PERCENT = 4

That could lead to good performance for that workload but could use all of the memory on the server. If you need another workload to run at the same time then you need to do configuration at the resource pool level. Suppose that you determine that the original workload can get by with 50% of the memory on the server. At the pool level you could set the following:

MAX_MEMORY_PERCENT = 50

And at the workload group level you could set the following:

GROUP_MAX_REQUESTS = 40
REQUEST_MAX_MEMORY_GRANT_PERCENT = 8

That would give you reasonable throughput for the first workload but also allow the second workload to run at the same time. The resource pool is used to divide resources between workloads and the workload group is used to divide resources between the queries that belong to the pool.


UPDATE Statistics with full scan Does running UPDATE Statistics on SQL 2008 r2 with full scan on a large table 50 million rows (non partitioned)? I am trying to confirm the following behavior.

a) takes any locks during the entire run? Not sure but I think no locks are taken however a sr. dba told me that it takes schema modify lock (hmm...)

b) cause blocking -- I don't think so

c) cause heavy IO -- as each and every page will be read into the memory and pollute the data page buffer cache. So generally you should do this off hours.

a) Pretty much anything takes a schema stability lock. You don't want something else changing the structure of the table while you are updating your statistics. According to this, update statistics takes schema stability and modification locks.

b) If something tries to change the table's structure, it will be blocked. IIRC, update stats does dirty reads, so it shouldn't block connections that are merely reading or writing.

c) If you use FULLSCAN, it will read the entire table because that is what you told it to do. I don't see how that can be seen as anything but 'causing heavy i/o'. Normally the default of 'sampling' works well enough, but I have seen it cause problems with data with non-homogenous distributions. Often, it's also easier to just reindex the whole table (especially if you can do it online) because reindexing is parallelizable where as update statistics isn't. (AFAIK, MS did not fix that in sql 2008.)


Restrict sysdba or system administrator from reading package in oracle 11g I have a requirement wherein the oracle database contains packages which have important stored procedures and functions which should not be exposed to the client / customer. The client / customer will be provided with a user account having EXECUTE privilege.

Is there any way in which I can protect the IPR of my package, so that as a owner I should only be able to change the content in the package for future updates.

I tried logging with SYS as SYSDBA and I could see the package and the package body.

I am a newbie to oracle. Please guide me on the steps that I need to take to secure the packages from being copied.

I think the only solution to this case is wrap the PL/SQL code.

The WRAP Utility and the DBMS_DDL Package


Hundreds of millions of rows: Index by composite BIGINT, VARCHAR, or CHAR of HASH? Given the following:

Target platform: MySQL 5.7, using InnoDB.

Scenario: Storing hundreds of millions of email addresses (plus some properties not used in queries). All queries will be done by knowing the email address beforehand.

Proposed solution:

SHA256 the email address.
Shard "the email table" by taking the first 3 bytes of the ASCII SHA256, creating 4096 tables (from 0x000 to 0xFFF) that will act as buckets for the email addresses. This tries to avoid having one single huge table.

Question: Which of the following would be a good PK to use inside each one of those 4096 buckets in terms of performance (indexing as being more important than querying)? Use of disk space might not be that important upfront (unless there are some heavy arguments to take this into account, which I'm open to know and discuss, of course).

VARCHAR(255) of the email address? This is of course the simplest.
CHAR(64) of the ASCII SHA256 hash of the email address? Long shot: I'm considering that indexing and comparing fixed length strings (CHAR) is faster than variable length strings (VARCHAR).
Split the SHA256 into 8 64bit integers, then create a composite PK of 8 BIGINT columns and index/query by those 8 BIGINT columns instead of using a VARCHAR/CHAR? Crazy idea: Perhaps using only 64bit integers for indexing and querying can provide a noticeable improvement in index and query performance (and also perhaps in disk access/storage). Although this is a composite PK of 8 BIGINT columns :\

Thanks in advance,

There is no advantage in any form of PARTITIONing based on what you have said.

The simplest, most obvious, index is also the best:  Have the PRIMARY KEY be the email address (if it is unique), or start with the email address plus something to make it unique.

This is possible

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT -- for uniqueness
...
PRIMARY KEY(email_addr, id)  -- id tacked on for uniqueness
INDEX(id)  -- to keep A_I happy.

What makes you think that "one huge table" is a problem?  No, PARTITION BY HASH does not buy you anything.

You said "shard" and mentioned 4096 buckets.  Technically that means you have 4096 servers to put parts of the data on.  I assume you really meant PARTITION or (yuck) 4096 manually-handled tables, which is limited to a single MySQL instance.

If you really did mean spread across multiple servers, then, sure, the bottom chunk of the SHA256 is a good idea.  MD5 is probably faster and just as good for this purpose.  I would then use a dictionary lookup with 4096 elements to point to however many servers you currently have.


MySQL: Point in Time query using InnoDB log sequence number? Is it possible to do a point in time query based on a log sequence number?

For example, I would like to do something similar to Oracle's "as of" query

select foo from bar as of scn 123923455;

which returns a read-consisten result as of that timestamp or system change number.

My application is verifying that the state of two independent database servers is consistent as of a certain operation or point in time.  The data is being replicated via Golden Gate with a Java adaptor, and I intend to store periodic "checkpoints" saying "The data should be consistent as of Oracle SCN x and Innodb log sequence number y."  I should be able to then run a program to verify this is the case.

it is no similar mechanism to retrieve record state in the past from inside MySQL using LSN.

based on business idea, You can save timestamped state of important data by trigger or procedure.


What does the Service Master Key protect? I am looking for a complete list of items that the Service Master Key is used to protect in SQL Server 2008.

Look at the encryption hierarchy found  in this TechNet article.  This document from MS shows the entire hierarchy.  You can see that the Master Key is created from the Windows DPAPI service and is used for:

-DB Master Key

-Certificates

-Symmetric Keys

-Asymmetric Keys

-TDE

-Transact SQL Encryption Functions

-Passwords (I'm not 100% on this, but it is in the technet article and it was mentioned in the comments so I added it)

Was this for a school project or a cert exam? Do you need any more background info?


Error 9002. The transaction log for database is full due to 'NOTHING' In the SQL Server Error Log (and Windows Event Viewer) I found the error message:

  The transaction log for database [...] is full due to 'NOTHING'.

The database is in SIMPLE recovery model.
Its log has 126 VLFs.
It is a user database, not a system one.

What does a reason of "NOTHING" mean?

If there is no reason which prevents the log from being reused, then why does this error message exist?

NOTHING is basically a 'Not any of the other reasons' status. Other Log Full status values I've seen are: LOG_BACKUP, REPLICATION, and CHECKPOINT.

As to the cause, check the log file max size & growth properties. Then compare them to the available disk free space. The log file may have reached the Max size, or the disk may not have enough free space to accommodate the next growth interval/amount.


Get the remainder of SUBSTRING_INDEX I have a problem regarding SUBSTRING_INDEX.

As the image shows, I can get the city by

SELECT SUBSTRING_INDEX(location,'-',1)) FROM my_table

However for the state_country column, I need the value which is after the '-'.

Is there a way to get that value and add it to the state_country column?

Thanks in advance.

To resolve this, I did the following:

Sample data table.

CREATE TABLE city_state
(
  location VARCHAR(100),
  city VARCHAR(100)
);

Sample data.

INSERT INTO city_state VALUES ('Minneapolis - Minnesota', 'Minneapolis'); -- note, space between hyphen and Minnesota
INSERT INTO city_state VALUES ('Orange County - CA', 'Orange County');
INSERT INTO city_state VALUES ('Denver - Colorado', 'Denver');
INSERT INTO city_state VALUES ('Dowingtown-Pennsylvania', 'Downingtown');  -- note, no space between "Downington" and "Pennsylvania" - also,
INSERT INTO city_state VALUES ('Manhattan-New York', 'Manhattan');
INSERT INTO city_state VALUES ('Ventura-California', 'Ventura');
INSERT INTO city_state VALUES ('Winston-Salem-North Carolina', 'Winston-Salem');  -- note, compound place name, i.e. Winston-Salem

For the Winston-Salem example, check here!

As pointed out by @Dudu Markovitz, you have to select from the last hyphen.

SELECT SUBSTRING_INDEX( location, '-', -1) FROM city_state;

But, there are also fields with spaces between the hyphen and those without, so a more complete answer would be:

SELECT TRIM(SUBSTRING_INDEX( location, '-', -1)) FROM city_state;

Minnesota
CA
Colorado
Pennsylvania
New York
California
North Carolina

Note, no spaces preceding (or following) the location name.