Efficiently write SELECT queries for tables with lots of columns (not SELECT *) Due to limitations in my GIS software, I need to write select queries for each of my 200+ tables in my database. The queries need to select all columns, except for one, the SHAPE column.

Is there an efficient way to get all the field names for each table - for the purpose of writing select queries?

What I've tried:

I think this is possible with SQL Developer(free). But unfortunately, I don't have SQL Developer, due to I.T. challenges.
I'm aware that when I create a SELECT * view on a table in Oracle, the query is translated to explicitly select individual fields. So I could a) create a SELECT * view for each table, b) get the view definition, and c) write the query for each table. This is probably quicker than manually typing each field name, but not by a lot.

How can I efficiently write select queries for tables with lots of columns?

We can use the ALL_TAB_COLS view along with LISTAGG to generate the queries that you need. This strategy should work as long as your tables don't go above 125 columns or so. If you have too many very long column names LISTAGG won't return the full value because the return value is limited to a VARCHAR2(4000). Below is one implementation:

SELECT 'SELECT ' || COL_LIST || ' FROM SCOTT.' || TABLE_NAME || ';' QUERY_TO_RUN
FROM
(
  SELECT DISTINCT TABLE_NAME
  ,  LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID)  OVER (PARTITION BY TABLE_NAME) COL_LIST
  from ALL_TAB_COLS
  WHERE OWNER = 'SCOTT'
  -- AND TABLE_NAME IN (...)
  AND COLUMN_NAME <> 'SHAPE'
) t;

In that example I'm using the built-in SCOTT schema (not sure if this is present in your database). Here are the results for me:

SELECT ENAME, JOB, SAL, COMM FROM SCOTT.BONUS;
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM SCOTT.EMP;
SELECT GRADE, LOSAL, HISAL FROM SCOTT.SALGRADE;
SELECT DEPTNO, DNAME, LOC FROM SCOTT.DEPT;


Difficult Query: Can Only Modify The WHERE Clause Table tblDistResell (the first row is the column headers):

DISTRIBUTOR RESELLER    STATE
ABC         BestBuy     CA
ABC         RadioShack  NJ
XYZ         BestBuy     CA
XYZ         RadioShack  MO

Query:

SELECT Distributor
WHERE
(
(Reseller = 'BestBuy' AND [State]='CA') OR (Reseller = 'RadioShack' AND [State]='NJ')
);

Resultset:

DISTRIBUTOR  RESELLER    STATE
ABC          BestBuy     CA
ABC          RadioShack  NJ
XYZ          BestBuy     CA

When EVERY row for a distributor is returned, we want the count (or the resultset; whatever's easier).

But if only ONE (or more) distributor record(s) aren't returned, we want to ignore all of that distributor's results.

In other words, given the example above, we want to see either,

DISTRIBUTOR  RESELLER    STATE
ABC          BestBuy     CA
ABC          RadioShack  NJ

or

count == 2

(No XYZ records, since the RadioShack, MO record is missing from the resultset.)

Here's one more example for clarity...

Let's use the same table, tbltblDistResell, but a modified query:

SELECT Distributor
WHERE
(
(Reseller = 'BestBuy' AND [State]='MO') OR (Reseller = 'RadioShack' AND [State]='NJ')
);

In this case, the following is returned:

DISTRIBUTOR  RESELLER    STATE
ABC          RadioShack  NJ

But since there is some ABC data that wasn't returned, we want to see either:

(0 row(s) affected)

or

count == 0

The challenge is that the tool we're using only allows us to modify the WHERE clause!

Any help would be greatly appreciated.

Greg

Count the number of rows for every distribution and compare that to the number of rows returned for a distribution where you apply your filter.

declare @T table
(
  Distributor char(3),
  Reseller varchar(10),
  State char(2)
);

insert into @T values
('ABC', 'BestBuy',    'CA'),
('ABC', 'RadioShack', 'NJ'),
('XYZ', 'BestBuy',    'CA'),
('XYZ', 'RadioShack', 'MO');

select T1.Distributor
from @T as T1
where (
      select count(*)
      from @T as T2
      where T1.Distributor =T2.Distributor
      ) =
      (
      select count(*)
      from @T as T3
      where T1.Distributor =T3.Distributor and
            (
            T3.Reseller = 'BestBuy' and T3.State='CA' or
            T3.Reseller = 'RadioShack' and T3.State='NJ'
            )
      );

select T1.Distributor
from @T as T1
where (
      select count(*)
      from @T as T2
      where T1.Distributor =T2.Distributor
      ) =
      (
      select count(*)
      from @T as T3
      where T1.Distributor =T3.Distributor and
            (
            T3.Reseller = 'BestBuy' and T3.State='MO' or
            T3.Reseller = 'RadioShack' and T3.State='NJ'
            )
      );


Will an SSRS report on Azure SQL Database work? Can we build an SSRS report using Visual Studio 2019 with Azure SQL server as PaaS mode?
We migrated to Azure Database from VM SQL Managed instance.

You can fetch the data from Azure SQL database but it does not include SSRS so you would need a separate install (Azure VM ?) for that.


How to put Oracle Redo Log file at the outer edge of disk for performance? I always read that the outer edge of disk is the fastest place. Somebody said "You better put your Oracle Redo Log file at the outer edge of disk". My question is:

How to put Redo Log file at specific location in disk?
How much performance will it will increase?

By the way I'm using solaris 10 for Oracle 11g Database

Updated:

Here is the information about my disk

/dev/dsk/c0t1d0s0 is currently mounted on /oracle/diag
/dev/dsk/c0t1d0s1 is currently mounted on /oracle/oradata01
/dev/dsk/c0t1d0s3 is currently mounted on /oracle/oraredo01
/dev/dsk/c0t1d0s4 is currently mounted on /oracle/oraredo02
/dev/dsk/c0t1d0s5 is currently mounted on /oracle/fast_recovery_area
/dev/dsk/c0t1d0s6 is currently mounted on /oracle/backup
/dev/dsk/c0t1d0s7 is currently mounted on /oracle/data

Virtual Drive: 1 (Target Id: 1)
Name        :
RAID Level  : Primary-1, Secondary-0, RAID Level Qualifier-0
Size        : 835.394 GB
Mirror Data : 835.394 GB
State       : Optimal
Strip Size      : 64 KB
Number Of Drives per span:2
Span Depth  : 3
Default Cache Policy    : WriteBack, ReadAheadNone, Cached, No Write Cache if Bad BBU
Current Cache Policy    : WriteBack, ReadAheadNone, Cached, No Write Cache if Bad BBU
Access Policy   : Read/Write
Disk Cache Policy   : Disk's Default
Encryption Type         : None

Oracle redo files are written in a sequential fashion. This is what gives the best performance because the write head is always on the correct location to write again, as long as the disk is not shared with other files. As soon as the disk - or more precisely - the write head is shared with a datafile, the head has to make more movements to satisfy the random IO pattern that is mostly found for datafiles.

The cylinders numbering of disks start at the outer edge of the disk. See Oracle Solaris 11 System Administration: Administering Storage Devices for some info. While formatting the slices you can point them their location on disk.

If you really want high performant redo files, give them their own dedicated disks and stripe them for best performance. Don't forget that the archiver is also going to read the redo after it has been completed ....

Managing files on separate disks is something of the past. Since years Oracle preaches SAME. Stripe And Mirror Everything. See Optimals Storage Configuration Made Easy Add the disks to an ASM diskgroup and let ASM handle the file placement.


innodb_buffer_pool & innodb_buffer_pool_instances I have a database dedicated server with one quad-core cpu and 48GB Memory.

Total database size is about 6GB and my ultimate goal is to get the maximum performance out of this system.

At First, to get maximum performance I set innodb_buffer_pool_size to '35GB' with innodb_buffer_pool_instance to 5, thinking that each instance will take up 7GB so total 35GB will be optimum.

But after several days, Innodb_buffer_pool_pages_free says I had 26GB free space in the buffer pool.

So I increased the instances number to 10 and waited some weeks, but the Innodb_buffer_pool_pages_free says about the same thing.

Was my calculation(expecting that 7GB * 5 instances would take up 35GB of buffer pool) wrong? Then how can I use all the free buffer pool size? Should I increase the buffer pool instances even more? Or run 2 mysqld processes to get replication or something?

If anyone get any clue, please give some hint to get the point right about my issue.

The number of pools does not change the total amount of pages innodb would use. A page exists on one of the pools, not a copy of it in each pool. That's why your 6GB database doesn't take much more than 6GB, the extra probably being for indexes.

From the documentation:

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances

  The number of regions that the InnoDB buffer pool is divided into. For
  systems with buffer pools in the multi-gigabyte range, dividing the
  buffer pool into separate instances can improve concurrency, by
  reducing contention as different threads read and write to cached
  pages. Each page that is stored in or read from the buffer pool is
  assigned to one of the buffer pool instances randomly, using a hashing
  function. Each buffer pool manages its own free lists, flush lists,
  LRUs, and all other data structures connected to a buffer pool, and is
  protected by its own buffer pool mutex.


Do you know an easy way to generate one record for each hour of the past 12 hours? I have a report that shows the count of events for the past 12 hours, grouped by the hour.  Sounds easy enough, but what I am struggling with is how to include records that cover the gaps.

Here is an example table:

Event
(
  EventTime datetime,
  EventType int
)

Data looks like this:

  '2012-03-08 08:00:04', 1
  '2012-03-08 09:10:00', 2
  '2012-03-08 09:11:04', 2
  '2012-03-08 09:10:09', 1
  '2012-03-08 10:00:17', 4
  '2012-03-08 11:00:04', 1

I need to create a result set that has one record for every hour of the past 12 hours, regardless of there being events during that hour or not.

Assuming the current time is '2012-03-08 11:00:00', the report would show (roughly):

Hour  EventCount
----  ----------
23    0
0     0
1     0
2     0
3     0
4     0
5     0
6     0
7     0
8     1
9     3
10    1

I came up with a solution that uses a table that has one record for every hour of the day.  I managed to get the results I was looking for using a UNION and some convoluted case logic in the where clause, but I was hoping somebody had a more elegant solution.

For SQL Server 2005+ you can generate those 12 records very easily with a loop ar a recursive CTE. Here is an example of a recursive CTE:

DECLARE @Date DATETIME
SELECT @Date = '20120308 11:00:00'

;WITH Dates AS
(
    SELECT DATEPART(HOUR,DATEADD(HOUR,-1,@Date)) [Hour],
      DATEADD(HOUR,-1,@Date) [Date], 1 Num
    UNION ALL
    SELECT DATEPART(HOUR,DATEADD(HOUR,-1,[Date])),
      DATEADD(HOUR,-1,[Date]), Num+1
    FROM Dates
    WHERE Num <= 11
)
SELECT [Hour], [Date]
FROM Dates

Then you just nedd to join it with your events table.


Performance tuning a Great Circle distance calculation I have a query that I am using to find locations that are within 1km of a known point.

To do this, I am using the Spherical Law of Cosines formula with my latitude and longitudes.

Currently, the query runs over roughly 5000 records in about 5 minutes. This is acceptable, but I would like to make it as fast as possible.

The main query I am using:

select loc.*, base.key
from locations loc left outer join baseline base
on base.key in
(select key
from baseline
where [dbo].CIRCLEDISTANCE(loc.LAT, base.LATITUDE, loc.LONG, base.LONGITUDE) <= 1)

The Circle Distance scalar value function:

ALTER FUNCTION CIRCLEDISTANCE
(
    -- Add the parameters for the function here
    @LAT1 varchar(250),
    @LAT2 varchar(250),
    @LNG1 varchar(250),
    @LNG2 varchar(250)
)
RETURNS float
AS
BEGIN

    DECLARE @Distance float
    DECLARE @LAT1_FLOAT float
    DECLARE @LNG1_FLOAT float
    DECLARE @LAT2_FLOAT float
    DECLARE @LNG2_FLOAT float

    select @LAT1_FLOAT = cast(@LAT1 as float)
    select @LNG1_FLOAT = cast(@LNG1 as float)
    select @LAT2_FLOAT = cast(@LAT2  as float)
    select @LNG2_FLOAT = cast(@LNG2 as float)

    SELECT @Distance = acos(sin(radians(@LAT1_FLOAT))*
            sin(radians(@LAT2_FLOAT))+
            cos(radians(@LAT1_FLOAT))*
            cos(radians(@LAT2_FLOAT))*
            cos(radians(@LNG2_FLOAT)-radians(@LNG1_FLOAT ))
            )*6371;

    RETURN @Distance

END
GO

The execution plan (with some anonymizing)

I have indexed all columns named in both query and function.

Some caveats:

I am converting to float in the function because the data I am getting arrives as a string and I am unable to change it prior to this point
The left outer join is required because I need to know locations that do not have a known point
I am using the Law of Cosines rather than Haversine because of it's simplicity and I don't require a large amount of accuracy, since I am only interested in things within 1 km.!

I am not a DBA myself, merely a .net developer, so I am not really knowledgeable about improving query performance beyond indexing, so any help is appreciated.

EDIT:

Rolling in Rob and Mark's answers allowed my to get my query execution down to 30 seconds. In case there's any further gains to be had, here is the query now:

select <columns>
from locations loc
left outer join baseline base
on base.key in
(select key
from baseline
where base.location_geo.STDistance(loc.location_geo) <= 0.00015678559)

The 0.00015678559 is 1000/6378137 (Earth radius) so I don't need to do any math when the query is run.

The location_geo is a computed column using the formula:

([geography]::STGeomFromText(((('POINT('+[Longitude])+' ')+[Latitude])+')',(104001)))

104001 is the Spatial ID for a spherical Earth model, since I'm not looking for a large distance this seems to make the query quicker to return.

I also have a Spatial index on the location_geo column as suggested here: http://social.technet.microsoft.com/wiki/contents/articles/9694.tuning-spatial-point-data-queries-in-sql-server-2012.aspx

Create a computed column which converts your lat and long columns into a geography type using the Point constructor. Then put a spatial index on this computed column.

Then your query can create a geography point from your circle centre, and compare distances. Should be very quick.


MySQL GROUP_CONCAT Query, is there a better way? I have the below Data / Query. Is this really the most efficient way of doing this? As it seems like a lot of work to get the result.

I've tried reducing it / combining the parts  but this is the only way I can get it to work.

(n.b. The actual table has a lot more entries over multiple days - hence the WHERE filters at the end)

CREATE TABLE test.`actest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `LS` varchar(25) NOT NULL,
  `ET` datetime NOT NULL,
  `STATUS` int(11) unsigned DEFAULT NULL,
  KEY `ix_id` (`id`)
) ENGINE=InnoDB;

INSERT INTO test.actest (`LS`,`ET`,`STATUS`)
VALUES ('aaa',NOW(),'123'),('aaa',NOW(),'123'),('aaa',NOW(),'123'),('aaa',NOW(),'456'),
('aaa',NOW(),'456'),('aaa',NOW(),'789'),('bbb',NOW(),'123'),('bbb',NOW(),'123'),('bbb',NOW(),'456');

SELECT DATE(`ET`) AS ED, `LS`, GROUP_CONCAT(DISTINCT(cnt2) ORDER BY cnt2 SEPARATOR ', ') AS STATUSES
FROM
    (
    SELECT `ET`, `STATUS`, `LS`,CONCAT(`STATUS`,' (',cnt,') ') AS cnt2
    FROM
        (
        SELECT `LS`,`ET`,`STATUS`,COUNT(`STATUS`) AS cnt
        FROM actest
        GROUP BY `LS`, `STATUS`
        ) a
    ) b
WHERE `ET`
    BETWEEN CONCAT(DATE_SUB(CURDATE(), INTERVAL 0 DAY),' 00:00:00')
    AND CONCAT(DATE_SUB(CURDATE(), INTERVAL 0 DAY),' 23:59:59')
AND `STATUS` > 0
GROUP BY `ED`,`LS`;

Result:
ED_________| LS__| STATUSES__________________
2015-10-01 | aaa | 123 (3) , 456 (2) , 789 (1)
2015-10-01 | bbb | 123 (2) , 456 (1)

Also, is there an easy way to get a TOTAL Count for each LS field, e.g

aaa | 6
bbb | 3

Don't you need to group the inner stuff by ED as well?

I think the inner 2 selects can be combined:

( SELECT  `ET`, `STATUS`, `LS`,
          CONCAT(`STATUS`, ' (', COUNT(*), ') ') AS cnt2
    FROM  actest
    GROUP BY  `ED`, `LS`, `STATUS`
) as b

To get the "total count", add an expression that has a correlated subquery:

( SELECT COUNT(*) FROM actest WHERE LS = b.LS AND DATE(ET) = b.ED )  AS TotalCount,

Putting it all together... (I'm not sure I got it right.)

SELECT  DATE(`ET`) AS ED,
        `LS`,
        ( SELECT  COUNT(*)
            FROM  actest
            WHERE  LS = b.LS
              AND  DATE(ET) = b.ED ) AS TotalCount,
        GROUP_CONCAT(DISTINCT(cnt2) ORDER BY  cnt2 SEPARATOR ', ') AS STATUSES
    FROM
      ( SELECT  DATE(ET) AS ED,
                `STATUS`,
                `LS`,
                CONCAT(`STATUS`, ' (', COUNT(*), ') ') AS cnt2
            FROM  actest
            GROUP BY  DATE(ET), `LS`, `STATUS`
      ) as b
    WHERE  ET >= CURDATE() - INTERVAL 7 DAY
      AND  ET >  CURDATE() - INTERVAL 0 DAY
      AND  `STATUS` > 0
    GROUP BY  `ED`, `LS`;

(I simplified the date expression, and showed how to get the 7 days leading up to midnight this morning.)
(I suspect I mangled some details; check carefully.)


Isolation and atomiticy of SQL Server UPDATE Is there any difference in atomicity and isolation between the two following SQL statements?

Batch A:

UPDATE Orders
SET InProgress = 1
WHERE Completed = 0

and

Batch B:

UPDATE Orders
SET InProgress = 1
WHERE OrderID in (SELECT OrderID FROM Orders WHERE Completed = 0)

Under the default isolation level in SQL Server of READ COMMITTED, I believe you could have concurrency issues with the batch B. After the subquery select, another process could set Completed to 1 on some of the selected rows, but batch B wouldn't know it and would still set InProgress = 1 on them.

But what about batch A? Could another process change Completed on any rows before InProgress is set to 1 on them by batch A? Or is it atomic and isolated from any other process?

Another way to ask this is, when is the update (or exclusive) lock placed on the table under READ COMMITTED isolation? In batch A, I'd assume it happens before the WHERE clause is evaluated, making the batch atomic and isolated. What about batch B? Is the update/exclusive lock placed on Orders before or after the subquery is evaluated?

The rows that qualify for an update are always stabilized during discovery by using (at least) U locks (Update locks, see Lock Compatibility). When is time to actually update them, the U lock is upgraded to X lock. Because of this stabilization the row cannot disappear nor can it be modified between the discovery and the update. Nor can a second UPDATE statement discover the same row: a concurrent UPDATE will have to wait for first to commit even if it only needs to 'check' the row and not actually update it. So Batch A is 100% bulletproof. For Batch B, most times the engine will understand what you mean and scan the join with update semantics (your IN clause will be rewritten as a JOIN during query normalization).

There are also differences in behavior depending on whether the plan requires Halloween Protection or not, but that only means the U locks is longer lived or not.

As a general comment the queries you show are the hallmark queries of using table row state where you should be using queues. The concurrency of SET InProgress = 1 WHERE Completed = 0 is basically 1, because it will always require a scan. An equivalent solution using queues for events to signal requests to transition state will scale basically X CPUs.


Is it safe to use the Windows "Compress directory to save space" feature on the directory that contains my MySQL general log file? I have changed MySQL 8 on my Windows 10 development machine to write logfiles (general log and slow queries log) to "E:\mysql logfiles". I'd like to compress this directory using the Windows "Compress Directory to save space" advanced feature in the Windows 10 directory properties to save space (currently my logfile is over 50 GB). I'm wondering though: is this a good idea? Or will this break MySQL in (subtle or not) ways?

The Windows feature to compress a directory is transparent to all applications.  However, it does slow down I/O operations some.
The General Log should not be left on forever; it is a big disk hog.  Instead, turn it on only long enough to get the necessary debugging info, then turn it off and toss the log file.
Or, you could log to TABLE and not FILE; I think the table has a limit.  (Check the settings.)


Any reason for declaring a view with cross-database SELECTs for the same database? I currently have to take over an old project and found a single view that's kind of a special snowflake because it's declared this way:

USE [DbName]
GO

create view [abc].[vXyz] as
select id, name, etc
from [DbName].[abc].[xyz]

Is there any good reason to do this? Only thing I could maybe think of is that this view is/was maybe also accessed from another database and whoever wrote it thought they would need to declare it this way to work properly, but as far as I'm aware it's not.

There is no need to specify the database if the view is in the same database.
And the view will work even if it is referenced by a different database.

So no no good reason of specifying the database when you are in the same database.


Generate a list from columns that do not contain a zero for each row I have a table TAB1 in an Oracle database:

+----+----+----+----+----+----+
| ID | C1 | C2 | C3 | C4 | C5 |
+----+----+----+----+----+----+
|  1 |  1 |  0 |  3 |  0 |  5 |
|  2 | 11 | 22 |  0 | 44 |  0 |
+----+----+----+----+----+----+

I need a select statement that gives me the following output:

+----+-------------------------+
| id |          descr          |
+----+-------------------------+
|  1 | column1,column3,column5 |
|  2 | column1,column2,column4 |
+----+-------------------------+

...meaning I need in to concatenate names for every column (not exactly the name of the column but a different text for every column) that is not 0.

Something like (untested):

select
  id,
  rtrim(
    case when c1 != 0 then 'column1,' end
 || case when c2 != 0 then 'column2,' end
 || case when c3 != 0 then 'column3,' end
 || case when c4 != 0 then 'column4,' end
 || case when c5 != 0 then 'column5' end, ',') as descr
from tab1;


Using multiple disks for large InnoDB database I am in the process of setting up a large MySQL/InnoDB database on a server that has multiple physical hard drives.
My current configuration uses innodb_file_per_table, a setting I would like to keep.

Now, my database consists of hundreds of "small" tables, most a couple of megs to a few gigs in size.
All in all however, the database exceeds the size of the individual disks, so I would like to split the .ibd files across multiple drives automatically, preferably with the option to add more disks as needed - I would very much prefer not to be forced to manage these myself.

Are there options in mysql to have it split the files across drives or do I have to use something like LVM/software RAID0 to achieve this?

The best way to use multiple similar drives is to use RAID-0 striping.  While you are at it you could add a little bit of security by using RAID-5 - one drive is logically a parity for the others; you can suffer lost of any drive without loss of data.

Once you have RAID, you can increase write performance by having a "Battery Backed Write Cache" on the Raid controller.  This gives you near-instantaneous writes without loss of security.

By striping, you get better sharing of the I/O across the drives than you could possibly do manually.

By striping, you get one large (logical) drive, and do not have to manually worry about which ibd goes where.

I would suggest putting the largest 20% of the tables in .ibd files; the rest in ibdata1.  This becomes especially important if you have thousands of active tables.  Otherwise, the proliferation of .ibd files has other consequences on table caches, etc.  (Since this suggestion is not 'automatic', you may not want to do it.)

Unfortunately, you probably have to dump and reload the entire database to add drives to a RAID system.  LVM might have a distinct advantage if you want to avoid that.  (But it won't have the other advantages of RAID.


Execution plan doesn't show memory grant I installed SQL Server 2019 on a VM on Azure (DS11). I am trying to illustrate adaptive query processing using this post here. The issue is that when I hit  CTRL+L in SQL Server Management Studio to look at the execution plan I can't see the memory grant there.

My question now is whether I can only see the memory grant if I am logged in as an admin? Or does SQL Server 2019 have a complete different query engine than SQL Server 2017?

Memory is granted at runtime, so the information you are looking for can only be seen in a post-execution (a.k.a "actual") execution plan, not a pre-execution ("estimated") plan.

If you are using SQL Server Management Studio as a client, CTRL-L is mapped by default to show an estimated execution plan. You can turn on post-execution plans using CTRL-M.

The tooltip on the root node of the plan only gives very limited information. For more detail, open the Properties window and look at the memory grant information there when selecting the root node of the "actual" execution plan.


Permission error when executing a 5.6 GB sqlcmd file I am trying to run a very large file through the sqlcmd command and it works perfectly with files of 100 MB up to 1.4 GB. This is what I have tested so far, but when I try to run a file which is 5.6 GB, it throws this permission error:

  Sqlcmd: Error: Error occurred while opening or operating on file C:\Test\Insert_Generated_File.sql (Reason: Access is denied).

The error is thrown immediately.

Are there any limitations on SQL Server Express edition when executing large files?

Just so that everything is clear, the file contains INSERT generated statements from a 4GB database and nothing more. It is saved as a *.sql file.

The version of sql I'm using is:

  Microsoft SQL Server 2014 - 12.0.2269.0 (X64)
      Jun 10 2015 03:35:45
      Copyright (c) Microsoft Corporation
      Express Edition (64-bit) on Windows NT 6.3  (Build 10586: ) (Hypervisor)

All I want to know is why am I getting that error, is it because of Express Edition, or because sqlcmd cannot handle a file that large. Unfortunately, I do not own a Standard Edition of SQL Server so I cannot test it there.

I know of the SQL Express database limitations, yet I am scripting a database which is already part of my server. It would make sense if I did not have this data already in, but I do. And all the data is already in once. I just cannot recreate it using this file.

@Mihai, As  Daniel Hutmacher already said,Express Edition is limited in database size. As per sqlmag BOL http://sqlmag.com/sql-server-2012/sql-server-2012-express-editions, if you are using SQL Server Express with Advanced Services. It is limited to 4GB and can access only the local instance.

Even in dba.stackexchange the Shanky How many users are supported on SQL Server 2012 Express? has also explain the limitation of SQL Server Express edition .


How do I merge these two queries so that the result set is "combined" and consistent? Please consider my first query:

SELECT cf.cnum as cnum, rc.name as name, SUM(rs.dh_simple + rs.ha_simple) as sums
FROM retirement_survey as rs
INNER JOIN currentfunds as cf
ON rs.fundid = cf.fundid
INNER JOIN research..complex rc
ON cf.cnum = rc.cnum
WHERE rs.date  = '9/30/2016'
GROUP BY rc.name, cf.cnum
HAVING SUM(rs.dh_simple + rs.ha_simple) > 0
ORDER BY cf.cnum

This gives me the following resultset:

Here is my second query:

SELECT r.cnum as cnum, c.name as name, r.assets as assets
FROM research..complex c, research..retirement_simple_ira r
where r.date = '9/30/2016'
and c.cnum = r.cnum
ORDER BY cnum

Which gives me the following resultset:

I've been trying so many different variations of this and joining the relevant tables together, but I am not able to get a consistent result. What I eventually want is a resultset with four columns:

cnum | name | sums | assets

Please help me with this.

Being somewhat lazy the easiest way to handle it is with a FULL OUTER JOIN and subqueries.  I'm making the assumption this is SQL Server btw.

SELECT ISNULL(x.cnum, y.cnum) cnum, ISNULL(x.name, y.name) name,
    x.sums, y.assets
(
    SELECT cf.cnum as cnum, rc.name as name, SUM(rs.dh_simple + rs.ha_simple) as sums
    FROM retirement_survey as rs
    INNER JOIN currentfunds as cf
    ON rs.fundid = cf.fundid
    INNER JOIN research..complex rc
    ON cf.cnum = rc.cnum
    WHERE rs.date  = '9/30/2016'
    GROUP BY rc.name, cf.cnum
    HAVING SUM(rs.dh_simple + rs.ha_simple) > 0
) x
FULL OUTER JOIN
(
    SELECT r.cnum as cnum, c.name as name, r.assets as assets
    FROM research..complex c, research..retirement_simple_ira r
    where r.date = '9/30/2016'
    and c.cnum = r.cnum
) y
    ON x.cnum = r.cnum
ORDER BY ISNULL(x.cnum, y.cnum)


Transaction Replication as DR solution I have a production database server and a failover database server set up.
All the transactions from the primary production db server is being  replicated(transactional replication with initialize from backup) to the failover database server and it is working fine.

No activities(no reporting) are being done in failover server ,it just gets all the changes from primary production database.
Even the reporting(reporting is minimum) is done from the primary production database server.

The goal is to use it as a failover server and i know that transactional replication is not a good idea for DR and there are other recommended solutions.
But this is the way it was setup by the previous DBA and it sounded okay during that time,i heard.
So if i have to failover,i have to do it manually as i understand by researching on this topic.

There are 3 application servers(app1,app2,app3) managed using the Load Balancer.
One database server(currently primary production server)

I just have a vague idea on how to do failover, by doing my own research.
I have two questions listed below:

Can someone be generous and direct me with steps on how to do the failover to the replicated database server.
Once i failover ,how do i manage the new transactions/changes back to the primary production database server.

The Transactional replication is not a "DR Solution" you may face a lot of issues with this, (Not all tables replicated, Different data in each DB) even that it worked in the past, There's no warranty it will work in the present. The suggestion will be using some supported tool like log shipping or always on.

Even with that if you decided to go with this, as @Queue Mann said, you will need to point everything to the new server, There's no automated option on this, (but there IS in Always on)
For your questions: A) There's no "official" fail over, you just use the replicated DB and point all your applications to the DB.

B) In the case of fail over you will need to set up everything again (In Always on , you won't as long it was done in Synchronous mode).

To be honest I don't know why keep using replication instead of the official DR solutions, you will save a lot of configuration (and troubles) going in the right way.


How to write marker messages into the MySQL general_log? I'm build a performance testing harness for our distributed web app. I'm using JMeter to drive it by simulating poorly performing parts of the app from the web side. I've set up MySQL to log to the general_log table. Now I would like to have JMeter send marker messages into the general_log so that, after identifying a poorly performing segment, I can fetch all of the queries run between markers surrounding that segment for further analysis.

I've tried sending an INSERT query on the table, but it always returns Error : You can't use locks with log tables. and my row is not created (but the INSERT query itself shows up).  I was hoping to send a customized message, but this doesn't work.

So, how do I insert a test run specific message into the general_log? Alternately, is there another solution? I was thinking of adding a table similar to the general_log table for my use, but we have a variety of deployments and being able to run this test on any of them without modification would be ideal.

Since you have mysql.general_log already converted to MyISAM and indexed, I would recommend making snapshots of that table based on a timestamp range. What I mean by snapshot is a temp table that contains just the events you wish to mark.

Suppose you want to log entries from the last 10 minutes. This Dynamic SQL should do it for you :

SET sql_log_bin = 0; ## Do this so it does not replicate to slaves
CREATE DATABASE IF NOT EXISTS myloggingdatabase;
USE myloggingdatabase
SET @now = NOW();
SET @ten_minutes_ago = @now - INTERVAL 10 MINUTE;
SET @snapshot = CONCAT('general_log_',DATE_FORMAT(@now(),'%Y%m%d_%H%M%00'));
SET @sql = CONCAT('DROP TABLE IF EXISTS ',@snapshot);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = CONCAT('CREATE TABLE ',@snapshot,' LIKE mysql.general_log');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = CONCAT('INSERT INTO ',@snapshot,' SELECT * FROM mysql.general_log');
SET @sql = CONCAT(@sql,' WHERE event_time>=',QUOTE(@ten_minutes_ago));
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

If you want all the events from yesterday, do this

SET sql_log_bin = 0; ## Do this so it does not replicate to slaves
CREATE DATABASE IF NOT EXISTS myloggingdatabase;
USE myloggingdatabase
SET @now = NOW();
SET @midnight_today = DATE(@now) + INTERVAL 0 SECOND;
SET @midnight_yesterday = @midnight_today - INTERVAL 1 DAY;
SET @snapshot = CONCAT('general_log_',DATE_FORMAT(@midnight_today(),'%Y%m%d_%H%M%00'));
SET @sql = CONCAT('DROP TABLE IF EXISTS ',@snapshot);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = CONCAT('CREATE TABLE ',@snapshot,' LIKE mysql.general_log');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = CONCAT('INSERT INTO ',@snapshot,' SELECT * FROM mysql.general_log');
SET @sql = CONCAT(@sql,' WHERE event_time>=',QUOTE(@midnight_yesterday));
SET @sql = CONCAT(@sql,' AND event_time<',QUOTE(@midnight_today));
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

You will to have discard those temp tables now and then by simply running

DROP DATABASE IF EXISTS myloggingdatabase;

GIVE IT A TRY !!!


When is space be allocated in databases? I came from the developer-side, not the administration.
But here and there I have to choose the datatype when I add a new column in our server (MS SQL or MySQL).

I want to add a column for numbers, so my choice is int or tinyint. At the moment I choose tinyint just because I know I want to store a value between 0 and 100 (a percent value) and it can't be higher.

But is there a difference for the space needed on HDD?
Basically: When is space allocated in databases?

When I create a nullable column for tinyint instead of int (1 byte instead of 4 bytes), and I insert a row of data with NULL for this column, is there still space allocated? Or just when I enter a number?

When I create a int column and just save a 1 into it, is there still 4 bytes allocated?

(This Answer addresses MySQL with ENGINE=InnoDB.)

Your specific question:  Use TINYINT UNSIGNED instead of INT to save 3 bytes per row (on disk and in cache).  Do it.

Basic guidelines (and TMI):

All numeric types (at least for NOT NULL) are fixed length, regardless of the value stored in them:

TINYINT - 1 byte
INT - 4 bytes
DOUBLE - 8 bytes
DECIMAL(m,n) about m/2 bytes.  (The exact number of bytes is complicated.)

SIGNED vs. UNSIGNED has not impact on space.  Instead, it impacts the range for *INT types.  Eg, TINYINT SIGNED is -128..127; TINYINT SIGNED is 0..255.

NULL vs NOT NULL -- the space consumed is complex due to MVCC, potential for ROLLBACK, the storage in BTree structure, etc.  It is not worth thinking of NULL as a way to save space.  Decide between NULL vs NOT NULL based on business logic, not space.  OK, you could let 0 or '' be used instead of NULL.  But, again, the space difference is so minuscule and complex to compute that it is not worth it.
 I think that NULL vs NOT NULL either matters or does not, depending on the ROW_FORMAT.

Make the decision between a 4-byte INT and a 1-byte TINYINT (or other sizes) when you originally create the table; it is costly to change it later.  Be conservative; maybe there are only 10K thingies today, but if the landscape changes, might you exceed the 64K limit of SMALLINT UNSIGNED tomorrow?

Most database data can be UNSIGNED.

String types (VARCHAR, TEXT, BLOB, etc) are typically variable length, with a 1- or 2-byte length prepended to the space.

CHAR(10) CHARACTER SET utf8mb4 NOT NULL takes 10 to 40 bytes, plus at least 1 byte overhead.  VARCHAR(10) CHARACTER SET utf8mb4 (NULL or NOT NULL) takes 0 to 40 bytes, plus at least 1 byte overhead.  (There may be ROW_FORMAT dependencies here, too.)  TEXT, VARBINARY, and BLOB act similar to VARCHAR.

Priority #1:  Business logic
Priority #2:  Space


Combining log shipping and regular log file backups Following on from my earlier question, I'm trying to set up a combination of Log Shipping and database backup. What I have in place is:

Log Shipping set up between my two servers, to run every 5 minutes
A (bespoke) process that takes a full backup every night at midnight, and log backups every hour

Log Shipping appears to be working OK; however, I cannot get my backups to run. The "full" backup is fine, but if I try to restore to a "point in time" after a few backups, I'm getting lots of errors similar to:

  The log in this backup set beings at LSN xxxxxxx, which is too recent to apply to the database. An earlier log backup that includes LSN yyyyyyy can be restored

Having dug around the logs and history tables, I can see that the "backup" performed by Log Shipping is chipping away at the transaction logs, and presumably "stealing" a chunk of the log, meaning there's a break in the chain of my backup logs.

I've tried changing my backup process to use COPY_ONLY when backing up the logs, but this hasn't made any difference.

Is it simply impossible to combine Log Shipping with regular full/log file backups, or am I missing something?

Your log backups are being done by the Log shipping process, that is after all what is there to do. So, you will generally not include log backups in your backup strategy but take into account how often your log shipping process runs.

You would only worry about taking full and/or differential backups against the databases involved in log shipping.


Table with N associations Suppose I have three tables: meta_tags, users, posts. (just an example)

Users and Posts both could have tags, so my meta_tags table could be structured like

 meta_tags
   - user_id
   - post_id
   - tag

But, I want my meta_tags table to be extendable. If I later create an item table that could also have a tag, I'd need to adjust my meta_tag table with a new _id column.

Would it be better to instead format my meta_tags table like

 meta_tags
   - thing_id
   - thing_type
   - tag

so that my query would be

 SELECT tag FROM meta_tags WHERE thing_id = 1 AND thing_type = "user";

vs

 SELECT tag FROM meta_tags WHERE user_id = 1;

or, maybe I should have separate tables? user_tags, post_tags, etc..

I guess my question is...which way is considered best practice? Are their significant performance impacts I should be aware of?

A way to resolve this would be to put the joining of things with tags into separate tables.  You would have your original tables users, posts, meta-tags and perhaps items.  In addition, you would have user-tags, post-tags, and perhaps item-tags.  The later three tables would only have the keys from the specific type table (e.g. user_id) and the key from the meta-tags table.

This design would also allow you to have other attributes to a tag that apply across all the base tables.


Data Warehouse Design and Double Dipping I know this may sound like a homework question, but I assure you it is not.
I am seeking for a second opinion about a lecture, usually I would go and ask the lecturer or tutors, but their answers usually leave me with more questions and sadly is a weekend and won't be able to sleep. And this time my Google techniques didn't work.

The question is about Double Dipping and it is based on one of my lectures.

Starting from this Star Schema (Lvl 1):

LVL 1 Star Schema

Which is from some olympic tables and the number of medals is my aggregated attribute.

Before going to the Double Dipping problem, we start with the Drill-Down.

The Lvl 1 is able to drill down from sport (swimming, etc) to events (4x100m relay women, etc), and its star schema (Lvl 0) would be this:

LVL 0 Star Schema

So the SportDimension was replaced with the SportEventDimension.
Because each event can have only 1 of each type of medals, then this would effectively be the Level 0, there is no real aggregation on the NumberOfMedals attribute.

In my Level 1, the aggregate function is SUM.
In my Level 0, the aggregate function is SUM. (I was told that there MUST always be an aggregate function to be considered a fact attribute, even if there will be no real aggregation).

But what if we want to Drill down even further? this time to see who was the athlete to participate in each event.

Then we could end up with the following star schema (lvl -1 (just to don't change the numeration of the previous levels)):

LVL -1 Star Schema

It looks nice... but it is wrong, it was mentioned in my lecture that this is because now we won't be able to answer questions like "how many gold medals did (country) get in 4x100m freestyle relay women".
It is also mentioned that this is because the Lvl -1 Fact Table has a different purpose, this time it has to do with athletes and not with sport events.
The result of trying to answer the previous question, would be 4 gold medals, because there are 4 participants, each one got their gold medal, but the country only was awarded 1 gold medal because it is only 1 event, and that is Double Dipping, getting more than the real value (as far as I understood).

Sample data, the number of gold medals should be 1, but now because of the Athletes dimension and the Double Dipping, we would get 4.

Double Dipping Example

To solve this, it was mentioned that we can create a multi-fact star schema. But at the end, it is mentioned that we can avoid the multi-fact and leave the level -1 as it is but mentioning that the AthleteDimension is Determinant.

Is there an actual effect just mentioning that the dimension is determinant to eliminate the Double Dipping?
My understanding for Determinant is that it is a dimension that MUST be used to give meaning to any query result. I don't quite get it how mentioning it will eliminate the Double Dipping from Level -1 and still being able to drill down from Level 0 successfully.

Can I not mention that is determinant, and use a "MAX" function when creating the report for the Level 0? (didn't rename levels, so the athletes level is still level -1, level 0 is the sport events and level 1 is the sport)

I was thinking on something like this when drilling down:
Number of medals:

Lvl -1 (Blue): SUM (although it will always be 1)
Lvl 0 (Green): MAX
Lvl 1 (Yellow): SUM

Proposed Report

I don't see why you can't have a factless fact table, with the Event, Athlete, and Country as dimensions. You are using the table to record events, rather than measures.

I guess that athletes can change countries for different Olympics, so it is a bit tricky to include them in the same hierarchy, though it could be a slowly changing dimension.

You would use Count(*) as the aggregation, instead of Sum(number_of_medals).


How can I convert from Double Precision to Bigint with PostgreSQL? I need to convert a value of Double Precision to Bigint with PostgreSQL. How can I do that?

I have tried with to_bigint(myvalue) but that function didn't exist.

There are two ways to typecast in Postgres:

You either do it the SQL standard way:

select cast(3.141593 as bigint);

or you could use the Postgres-specific cast operator: ::

select (3.141593 :: bigint);

You might also want to consider the various rounding functions.


Subquery in the VALUES clause Here's a real-world schema:

CREATE TABLE [dbo].[Setting]
  (
     [ID]       [BIGINT] NOT NULL,
     [Module]   [NVARCHAR](400) NOT NULL,
     [Property] [NVARCHAR](400) NOT NULL,
     [Value]    [NVARCHAR](4000) NULL,
     PRIMARY KEY CLUSTERED ( [ID] ASC )
  )

And here's the INSERT statement I'm trying to execute:

INSERT INTO [Setting]
            ([ID],
             [Module],
             [Property],
             [Value])
VALUES      ((SELECT COALESCE(max(ID), 0) + 1
              FROM   [Setting]),
             N'security.catalogs.integrated.integrated',
             N'enable-integrated-authentication',
             N'true')

For the life of me I cannot see anything special here, yet on at least two occasions this has failed on SQL Server 2008 R2 Express with the following error message:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

This INSERT statement operates on a completely empty table, no strange options were allegedly configured for SQL Server, no nothing.

How can this be happening? From my understanding of SQL Server, max cannot possibly return more than one row.

Experimentally, your code worked for me on both 10.50.1600 (2008 R2 RTM) Developer and 10.0.4000 (2008 SP2) Express. However, it failed against 9.0.5057 (2005 SP4 + hotfix). So, I dug into the documentation.

The 2005 version says this about expressions in the VALUES clause (emphasis mine):

  expression

  Is a constant, a variable, or an expression. The expression cannot contain a SELECT or EXECUTE statement.

while the 2008 and higher version looks like this:

  expression

  Is a constant, a variable, or an expression. The expression cannot contain an EXECUTE statement.

From this evidence, I can only assume that the server version where you're executing these queries is not what you think it is.


MySQL GRANT PROXY - what does it mean? I run:

show grants for root@localhost;

and I see

 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

From mysql docs:

https://dev.mysql.com/doc/refman/5.5/en/proxy-users.html

  This enables the external user to be a proxy for the second user; that
  is, to have the privileges of the second user. In other words, the
  external user is a “proxy user” (a user who can impersonate or become
  known as another user) and the second user is a “proxied user” (a user
  whose identity can be taken on by a proxy user).

But I am not understanding what they mean. I got the system from another worker who left the job and want to make sure if everything is secure and do not know if this grant is even needed. But if it does not make any security issues, I can leave it.

Can somebody explain in more simple way?

Update:

How can I connect as as another user? I was trying various combinations, of username password, but I cannot make it connect.

For example I used user root, but password from another user, which did not work. Tried another user and root password, also did not work.

Update:

Or this maybe means that root user can connect as other user? How to do that at least for example if so?

MySQL User Authentication is a rather lengthy process to explain. I refer you to my 3.5-year-old answer to MySQL error: Access denied for user 'a'@'localhost' (using password: YES) so you can see the steps a user takes to authenticate.
What the PROXY grant does is allow one user to masquerade as another user and bypass MySQL's normal but lengthy user authentication protocol.
Such proxy grants could be a security hole if one knew of this and started taking advantage of it. The quickest way to deactivate this would be to run
TRUNCATE TABLE mysql.proxies_priv;
FLUSH PRIVILEGES;

This will wipe out the current proxy privileges.
Afterwards, if you want to restrict proxy privileges, go to the OS and do this:
cd /var/lib/mysql/mysql
chmod -w proxies_priv.*

This will prevent new proxy privileges from being created.
GIVE IT A TRY !!!


Unable to add a new connection to server in PostgreSQL using pgAdmin I am new to PostgreSQL and trying to configure it. After installing the PostgreSQL. I am trying to add a new connection to server using pgAdmin. I know there is a by default created on port 5432 but I change this port to 5433. And trying to configure all my database on this port. And through terminal this is working.

But when I am trying to add a new connection to a server it throws

09:56:56: Error: Error connecting to the server: FATAL:  password authentication failed for user "tars"
FATAL:  password authentication failed for user "tars"

I have already followed this thread here. And also tried configuring pg_hba.conf. Changed this line

local   all             all                                     peer

to

local   all             all                                     md5

But couldn't add a new connection.
This is the screenshot, the way I am trying to configure

Can someone help me to create a new server ? And also help me to understand this little bit.

It seem like you are connection via termial to port 5432 as command psql on terminal directly connect to default port 5432

Try connecting to database via terminal using below

psql -p 5433

After connected, change user password

alter role tars with password 'tars'; --if role in not present create one with create role tar with superuser;
alter role tars with login;

now go to pgadmin and change the following :

on maintenance db put "postgres"
on user put "tar"
on password put "tar"

and try connecting if still kindly lemme know the below things :

1) Can u list contents of pg_hba.conf and postgresql.conf file ?

2) Also how u changed port number via postgresql.conf or created another cluster ?


MongoDB failover reasons I have a replica set on MongoDB with 3 members - primary, secondary and arbiter. One day I came and saw that primary and secondary have switched. I'd like to find a reason for the failover that caused the elections. Please advise me how and where I can find what caused it. I looked in the logs and I found only this:

  2015-03-03T12:18:41.540-0500 [rsHealthPoll] replset info
  my_server:27017 thinks that we are down 2015-03-03T12:18:41.540-0500
  [rsHealthPoll] replSet member my_another_server:27017 is now in state
  PRIMARY 2015-03-03T12:18:41.540-0500 [rsMgr] stepping down; another
  primary was elected more recently

Thanks

Here is what you can do to start

Run the following on the old PRIMARY

var dt = new Date(db.serverStatus().localTime - db.serverStatus().uptime*1000).toString() ; dt

This will print the exact time mongod was started.

PRIMARY failover was triggered 2015-03-03T12:18:41.540-0500.

If dt is close to 2015-03-03T12:18:41.540-0500

mongod crashed or was restarted
Check the mongod Log File on the old PRIMARY for that same timeframe

If dt is not close to 2015-03-03T12:18:41.540-0500

mongod is still running fine
The old PRIMARY simply became unreachable
Check network logs along port 27017


Do foreign keys affect performance in Oracle? Regardless of whether you create indexes for your foreign keys (which if course you should), does having foreign keys have any impact on performance in Oracle databases?

One specific area i was wondering about was whether they help the database produce better query plans

Yes.

The optimizer can remove redundant tables from a query when RI is enforced in the database.

For example, here are two tables:

create table t1 (
  t1_id int not null primary key
);

create table t2 (
  t2_id int not null primary key,
  t1_id int not null
);

The second only contains t1_ids from the first:

insert into t1
  select level from dual
  connect by level <= 100;

insert into t2
  select rownum, t1_id
  from   t1, (
    select * from dual connect by level <= 10
  );

commit;

So a count of T2 returns the same number of rows as a join of the two tables:

select count(*) from t2;

COUNT(*)
      1000

select count(*)
from   t1
join   t2
on     t1.t1_id = t2.t1_id;

COUNT(*)
      1000

But there's no FK defined :(

So the optimizer doesn't know this. And it needs to access both tables when executing the query:

set serveroutput off

select /*+ gather_plan_statistics */count(*)
from   t1
join   t2
on     t1.t1_id = t2.t1_id;

COUNT(*)
      1000

select *
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

PLAN_TABLE_OUTPUT
SQL_ID  8p235qbxm8yn0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */count(*) from   t1 join   t2 on
t1.t1_id = t2.t1_id

Plan hash value: 3484656271

----------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |      1 |        |      1 |00:00:00.01 |      10 |
|   1 |  SORT AGGREGATE     |              |      1 |      1 |      1 |00:00:00.01 |      10 |
|   2 |   NESTED LOOPS      |              |      1 |   1000 |   1000 |00:00:00.01 |      10 |
|   3 |    TABLE ACCESS FULL| T2           |      1 |   1000 |   1000 |00:00:00.01 |       6 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0014412 |   1000 |      1 |   1000 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."T1_ID"="T2"."T1_ID")

But add a foreign key to the mix:

alter table t2 add constraint fk foreign key ( t1_id ) references t1 ( t1_id );

And it now knows there can't be any t1_id values in t2 that don't exist in t1. So it can ignore t1:

select /*+ gather_plan_statistics */count(*)
from   t1
join   t2
on     t1.t1_id = t2.t1_id;

COUNT(*)
      1000

select *
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

PLAN_TABLE_OUTPUT
SQL_ID  8p235qbxm8yn0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */count(*) from   t1 join   t2 on
t1.t1_id = t2.t1_id

Plan hash value: 476902662

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |       6 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS FULL| T2   |      1 |   1000 |   1000 |00:00:00.01 |       6 |
-------------------------------------------------------------------------------------

Poof! It's gone! :)


drop postgres table with minimal locking I'm trying to run the following Postgres migration script

DROP TABLE some_table;
DROP TABLE another_table;

The problem is that this script causes excessive locking when run on the production database and therefore has to be aborted. Is there a way to reduce the locking? I've considered removing all indices on these tables and/or foreign keys before executing the DROP statements, but I don't have an easy way to simulate the production environment in order to test whether this will have a positive or negative influence on the locking.

My best guess about what you're talking about is that the process of obtaining locks on the tables to drop them takes too long. You're running the changes in a transaction, so you obtain an exclusive lock on some_table before moving on to try to obtain an exclusive lock on other_table. At this point, some_table is inaccessible to clients, but other_table still has queries running against it so the migration script can't get a lock yet. Queries start to block on some_table and production operations begin to stall. You might even have transactions deadlocking because they already have a read lock on other_table and are trying to acquire a read lock on some_table, the reverse order to your DDL migration script.

Presumably your migration script is going to re-create the tables with a new definition after dropping them, then re-populate them, since you wouldn't be dropping tables that are in production use otherwise.

If that's the issue, your options are limited. You can rearrange your migration script into a set of individual transactions that don't depend on each other, like:

BEGIN;
DROP TABLE some_table;
CREATE TABLE some_table ( new_definition );
INSERT INTO some_table (...) VALUES (...);
COMMIT;

BEGIN;
DROP TABLE other_table;
CREATE TABLE other_table ( new_definition );
INSERT INTO other_table (...) VALUES (...);
COMMIT;

... or do the same thing with ALTER TABLE transformations instead of a drop, re-create and re-load.

Alternately, you can REVOKE access to the tables then query pg_locks and pg_cancel_backend all connections that currently hold locks on the tables you want to modify, apply the migration, and then GRANT access again. That'll ensure the migration runs promptly at the cost of aborting some queries. A well-written application will just re-try, so this shouldn't be particularly disruptive.


Access Data stored in SQL Server I'm currently working on a project to convert our Access Apps to store the table data in SQL Server.

Using SSMA to migrate the data results in the foreign keys and Look-ups work as expected in Access when completed. However, if I import & link directly from SQL Server back to Access, the foreign key look-ups do not work.

The only difference I can determine in the links is, the ones SSMA created do not specify a DSN and the ones I manually created do.

I'm hopefully someone might have run into this in the past and might have some insight.

We are migrating from Access 2013 to SQL Server 2012.

I finally determined the lookups have the be setup in Access, they won't automatically be created when attaching a sql server table as a link.


If I actively BEGIN a transaction, but fail to ROLLBACK at error, will PG become confused? Suppose I have this application pseudo code (it's actually PHP and communicating with PG with the pg_* functions):
function handle_update_of_foo_table()
{
    BEGIN;
    TRUNCATE TABLE foo;

    loop (thousands of times)
    {
        INSERT INTO foo blablabla...;

        if (INSERT FAILED)
        {
            ROLLBACK;
            return false;
        }
    }

    COMMIT;
    return true;
}

handle_update_of_foo_table();
UPDATE unrelated_table SET blablabla;

Do I understand things right if I say that, had I not actively remembered to do the "ROLLBACK" before returning, when an INSERT failed for whatever reason, the transaction would have "continued on" to perform the unrelated UPDATE query inside the transaction, and thus, when the script ends right afterwards, it would have auto-rolled back because there had been a BEGIN but no COMMIT, so as far as PG is aware, all of my queries, including the unrelated UPDATE in the end, are part of the same transaction? And thus, the unrelated_table will not be updated in the end, because the entire transaction was auto-rolled back due to not COMMIT?
With me forgetting the ROLLBACK (which I did not forget in the example code, but might very easily do in reality somewhere), it has no way to know what my intentions were since it just sees a series of queries inside an explicitly started "transaction"?
If I have understood this, I think I finally "get" transactions. It only took me twenty years...

The transaction won't be "auto rolled back", it will be marked as failed, and the only option you have, is to do a rollback.
If you did not run rollback in the function but simply return from it in case of an error, then the UPDATE following the function call would result in "current transaction is aborted, commands ignored until end of transaction block" - so the UPDATE can't be run either (again: only if you omit the rollback in the function in case of an error).

Unrelated to your question, but: your UPDATE will (implicitly) run in a second (different) transaction if your insert succeeds because your function cleanly ends the transaction that was started in there. If you did mean to run insert and update in the same transaction, your code is not going to do that.


ERROR 2026 (HY000): SSL connection error: Unable to get private key I need help with mysql replication with ssl. I got a problem when i'm turn on ssl on my slave - error connecting to master 'replica@1.2.3.4:3306' - retry-time: 60  retries: 1

Here is my setup

Master

mysql> show variables like "%ssl%";
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| have_openssl  | YES                                  |
| have_ssl      | YES                                  |
| ssl_ca        | /etc/mysql/ssl-mysql/ca-cert.pem      |
| ssl_capath    |                                      |
| ssl_cert      | /etc/mysql/ssl-mysql/server-cert.pem |
| ssl_cipher    |                                      |
| ssl_key       | /etc/mysql/ssl-mysql/server-key.pem  |
+---------------+--------------------------------------+

Certs created on Gentoo with OpenSSL 1.0.2d    2015-07-09

Here's the interesting part. if I connect from Ubuntu 12.04 client instance specifying a client key, I get the standard error:

$ mysql -h 1.2.3.4 --ssl-ca=/etc/mysql/ssl-mysql/ca-cert.pem --ssl-cert=/etc/mysql/ssl-mysql/client-cert.pem --ssl-key=/etc/mysql/ssl-mysql/client-key.pem -u root -p
Enter password:
ERROR 2026 (HY000): SSL connection error

In logs

SSL error: Unable to get certificate from '/etc/mysql/ssl-mysql/client-cert.pem'
[ERROR] Slave I/O: error connecting to master 'replica@1.2.3.4:3306' - retry-time: 60  retries: 1, Error_code:2026

However, if I only specify the CA certificate and do not specify a client key or certificate, I can connect correctly with SSL:

mysql -h 1.2.3.4 --ssl-ca=/etc/mysql/ssl-mysql/ca-cert.pem -u replica -p

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.27, for Linux (x86_64) using  EditLine wrapper

Connection id:      6873
Current database:
Current user:       replica@1.2.3.5
SSL:            Cipher in use is DHE-RSA-AES256-SHA

You are trying to connect to the remote host using the server file of the master.

The reason it is working with only the CA is presumably the SSL configuration on the slave has it's own certificate signed by the same CA.

What you seem to be trying to do is use client certificate authentication.  If that is the case you need an additional client certificate that is recognised on the slave to be used as the certificate you use when trying to do a client connection.

IN simple terms, the server-cert is used to provide the SSL negotiation and is provided by the server to clients to allow the clients to encrypt their traffic in a way that the server can decrypt.

Each server would have its own server-cert that identifies its hostname and allows clients to connect and verify they are connecting to the expected server.

A client certificate is normally a separate certificate and is unrelated to the server certificate on either the master or the slave.


Problems while connecting to a newly installed PostgreSQL 9.2 on same box as 9.1 I have a new project at work that is using a PostgreSQL 9.2.  But, I'm still having to support  a project that uses 9.1.  So, I'm trying to configure my local dev box to have both installed.

I have gotten 9.2 installed and confirmed it runs fine.

However, I can't connect to it.

xxxxxxx@xxxxxxx-desktop-ubuntu:~$ sudo /etc/init.d/postgresql restart
[sudo] password for xxxxxxx:
 * Restarting PostgreSQL 9.1 database server                                                                     [ OK ]
 * Restarting PostgreSQL 9.2 database server                                                                     [ OK ]
xxxxxxx@xxxxxxx-desktop-ubuntu:~$ su postgres
Password:
postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$ psql -h localhost -p 5432
Password:
psql (9.2.4, server 9.1.9)
WARNING: psql version 9.2, server version 9.1.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=# \q
postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$ psql -h localhost -p 5433
Password:
psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"
postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$

So, how do I connect to this new instance?  I thought that the Ubuntu/OS postgres user would allow me to connect, but it doesn't.

Other info:

9.1 is on the default port: 5432
9.2 is on port: 5433
Using Ubuntu 12.04

You need to use

psql -p 5433

instead of

psql -h localhost -p 5433

because the default Ubuntu access control setup has password authentication over TCP/IP but peer authentication over Unix-domain sockets.


Migrating large database What is a quick way of migrating 75G database to the cluster? I tried to test the backup and restore and it took more than 2 hrs. Business owners won't accept the long outage. Any idea how I can make this fast or how to accurately do it? This is on SQL server 2008 R2.

One of the easiest ways to use a full backup to prepare, a differential to migrate.

First off, you do a full backup and restore to prepare the files on the new server disks, but use the RESTORE .. WITH NORECOVERY option to allow more restores.

At the time of migration, you do a differential backup/restore. This is far quicker and you use RESTORE .. WITH RECOVERY this time.


Postgres still connecting via SSL - despite invalid certificates I'm playing around with SSL connections to a test postgres server and I'm finding I can still initiate an SSL connection despite invalid certificates. (The valid date has expired)

I have enforced SSL connections in the pg_hba.conf

## pg_hba.conf - SSL TESTING
hostssl         mike            mike            192.168.56.106/32       md5

SSL Certificate is expired:

$ openssl x509 -in server.crt -noout -dates
notBefore=Feb 22 18:29:39 2019 GMT
notAfter=Feb 23 18:29:39 2019 GMT

Still able to connect via SSL:

postgres:/db/postgresql/10/data>psql "sslmode=require host=192.168.56.105 dbname=mike user=mike"
Password:
psql (10.7)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
mike=>

Why can I still connect?

I'm hoping for a situation where the connection is rejected because the certificate is invalid - am I missing something? Any help appreciated :)

The documentation says:

  By default, PostgreSQL will not perform any verification of the server certificate.

In other words, SSL is used for encryption.

  […]
  To allow the client to verify the identity of the server, place a root certificate on the client and a leaf certificate signed by the root certificate on the server.
  […]
  Once a chain of trust has been established, there are two ways for the client to validate the leaf certificate sent by the server. If the parameter sslmode is set to verify-ca, libpq will verify that the server is trustworthy by checking the certificate chain up to the root certificate stored on the client. If sslmode is set to verify-full, libpq will also verify that the server host name matches the name stored in the server certificate.


Error while connecting psql to PostgreSQL 9.6 I am using Postgresql 9.6 and getting an error while connecting psql.
Error- ./psql
psql.bin: FATAL:  the database system is starting up. What could be the issue?

In the log it says
2021-02-18 13:23:31.311 IST [85845]: [-] db=,user= LOG: started streaming WAL from primary at 3FC/91000000 on timeline 2
2021-02-18 13:23:31.312 IST [85845]: [-] db=,user= FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000002000003FC00000091 has already been removed

Your standby server has been down long enough that the primary doesn't have the required transaction log information any more. In addition, the standby was not shut down cleanly.
You will have to remove the standby server and rebuild it from scratch, unless you have a WAL archive that still contains the required segments.
Consider using replication slots next time to avoid that problem.


Pointing the database secondary data file towards a network drive I have a 2 TB database and the Data Drive has filled up. My network team cannot expand the drive nor provide another drive so that the secondary data file can be pointed towards that. My question is can the secondary drive be pointed towards  a network share .

You can do it, it doesn't mean you should. There is many things to take into account, like performances and reliability.
Putting the files on the network will probably cause you some performance problems as data needs to be moved across the network for every read, insert, update, delete.
If you have historic data, then maybe you could consider putting those older, less accessed data on the network. You would have less performance issue if you don't access the data often.
Then you might have some data corruption, for example if SQL Server is writing to the database, but the network fails. You have to keep that in mind.
That being said, if you want to do it anyways, you basically have to enable trace flag 1807 at the startup of your SQL Server and then you'll be able to do it.
I recommend you read those two references for a detail of the risks involved :

Microsoft's Documentation
Brent Ozar's blog

To transfer part of the database to the new file on the network, you should do the following operations:

You add another filegroup to your database and it's location will be on the network.
You need to recreate the clustered indexes or primary key clustered of the data that you wish to move to the network on the new filegroup.


Does large text datatypes reserves storage on disk? I'm design an DB for an app, in a table there is a column MEDIUMTEXT which according to mysql doc can storage on disk upto 16MB.
So my question is if I'm storing a text of 1Mb in this column, will it still reserves 16MB storage/block on disk for this particular record.
And also while reading all columns of multiple rows at once i.e 100 rows, what impact will have on memory and CPU usage for reading such large size storage columns?

Mot eactly, see mysql manual

TEXT and BLOB columns are implemented differently in NDB; each row in a TEXT column is made up of two separate parts. One of these is of fixed size (256 bytes), and is actually stored in the original table. The other consists of any data in excess of 256 bytes, which is stored in a hidden table. The rows in this second table are always 2000 bytes long. This means that the size of a TEXT column is 256 if size <= 256 (where size represents the size of the row); otherwise, the size is 256 + size + (2000 × (size − 256) % 2000).

As you can see it save 256 Bytes if you have equal or less than 256 bytes.
And if you have more than 256 bytes mysql reserved 200 Bytes blocks till it can store all data


Consequences of increase Oplog to large size I'm trying to add a new member (m*-server: 4.2.7) in my replica set (m*-servers: 4.2.1),
and it can't sync in time, after the initial sync it changes state to RECOVERING and have: (~ 4.3 - 6.31 hrs) behind the primary; Oplog size: "usedMB" : 50769.34, "timeDiffHours" : 1.19 ( ~0.79 -1.19).

I can't optimize queries or reduce size of dbs (~1.9T), or make backup and copy data (there is not enough time),
so think only solution for me is replSetResizeOplog on all members.

Question:
What the consequences can be I if increase size of Oplog larger than Upper Bound: 50GB (WiredTiger Storage Engine) to for example: 400 Gb?

Nothing negative, if you don't count the usage of disk space.  Actually you need just make your Primary nodes OpLog bigger and then wait at least that 6 hours to fill it enough. But of course it's good to have same size OpLog on every RS node.
I try to keep OpLog size at least 48h. That gives you "time" to solve problems, if those rise up.


Can I update every datetime in bulk? The current database assumes all times are local to the user. Now we're changing this so the database stores everything in UTC and our application converts the UTC time in the database to the correct timezone for each user.

Now we need to update every datetime in the database to UTC time. Is there a better way than having our application (which has a Timezone library) update each one individually?

Due to daylight savings time (BST), a simple DATEADD isn't an option. There are roughly 10 million cells to update.

Assuming you are dealing with a single time zone at a time (e.g. British Time), and can identify all rows that are in British Time (maybe that's everyone so far), you could create a calendar table that has the DST time change dates for Summer Time. You subtract an hour for all times that fall between, and don't touch the ones that don't. Sample data:

CREATE TABLE dbo.SampleData
(
  LocalDT DATETIME,
  IsBritishTime BIT,
  UTC DATETIME
);

INSERT dbo.SampleData(LocalDT,IsBritishTime) VALUES
 ('20000101 00:00',1), -- should NOT be changed
 ('20000326 00:59',1), -- should NOT be changed
 ('20000326 01:01',1), -- should go back
 ('20001029 01:59',1), -- should go back
 ('20001029 02:01',1), -- should NOT be changed
 ('20001231 23:59',1), -- should NOT be changed
 ('20000401 00:00',0); -- should NOT be changed (different time zone)

Now you can identify the ranges using some rather tricky T-SQL. We grab some rows from system tables based on the years your data needs to cover, then for each year we calculate the beginning and end of BST. We can then use those outputs to update the main table:

SET DATEFIRST 7;

;WITH y AS
(
  -- all the years from 2000 through 50 years after the current year:
  SELECT TOP (YEAR(GETDATE())-2000+51)
    y = DATEADD(DAY,-1,DATEADD(YEAR,number,'20000101'))
      FROM [master].dbo.spt_values
      WHERE [type] = N'P' ORDER BY number
),
s AS
(
  SELECT
    -- BST starts last Sunday in March @ 1:00 AM UTC:
    BSTStart = DATEADD(HOUR, 1, DATEADD(DAY,(1-DATEPART(
      WEEKDAY,DATEADD(MONTH,3,y))) % 7,DATEADD(MONTH,3,y))),
    -- and ends last Sunday in October @ 2:00 AM UTC:
    BSTEnd = DATEADD(HOUR,2,DATEADD(DAY,(1-DATEPART(
      WEEKDAY,DATEADD(MONTH,10,y))) % 7,DATEADD(MONTH,10,y)))
  FROM y
)
-- UPDATE d SET
SELECT *,
  UTC = DATEADD(HOUR,
  CASE WHEN s.BSTStart IS NULL THEN 0 ELSE -1 END, d.LocalDT)
FROM dbo.SampleData AS d
LEFT OUTER JOIN s
ON d.LocalDT >= s.BSTStart
AND d.LocalDT < s.BSTEnd
WHERE d.IsBritishTime = 1
-- or more likely WHERE EXISTS
-- (SELECT 1 FROM dbo.Users
--    WHERE UserID = d.UserID
--    AND TimeZone = 'BritishTime');

As written, this just shows you the join and identifies the rows that will be updated with a different UTC value (they will have non-null BSTStart/BSTEnd values). If you uncomment -- UPDATE d SET and comment SELECT *,, then select the data from dbo.SampleData, you should see this:

LocalDT          IsBritishTime UTC
---------------- ------------- ----------------
2000-01-01 00:00 1             2000-01-01 00:00
2000-03-26 00:59 1             2000-03-26 00:59
2000-03-26 01:01 1             2000-03-26 00:01 -- changed
2000-10-29 01:59 1             2000-10-29 00:59 -- changed
2000-10-29 02:01 1             2000-10-29 02:01
2000-12-31 23:59 1             2000-12-31 23:59
2000-04-01 00:00 0             NULL

(And actually, the March 26 1:01 local time shouldn't be possible - at 1:00 AM, the clocks rolled forward to 2 AM, so how could it ever have been 1:01 AM locally? So, you may want to special case that hour and deal with those cases individually. This is why every system I design stores UTC from the start - there is no plausible reason to store local time, since I can always get that from UTC, but I can't always get back.)

Of course, this will affect all rows that match the WHERE clause, it's just that some of the rows won't have a different value for UTC. I highly recommend putting this into a different column first, rather than just changing the start time, so that you maintain the original data in case something goes wrong, you need to troubleshoot, you do end up needing that data later, etc. You can change the column names if you want to use the UTC value in existing queries instead of changing those one-by-one.

This will work best if you have supporting indexes on the underlying table. If you are performing this update against multiple time zones (and have a column that can identify rows by local time zone), then creating a filtered index for each operation might be something worth trying. If you are identifying by some other indirection (such as UserID), maybe you could do a user at a time, or the set of users from each time zone. Chunking will work best in terms of blocking and log impact, but having no other details about the structure and how you could partition the data, that's the best I can do.

I provided British Time above, since it was the example in the question, but you'll obviously need different calculations for different timezones that have different offsets and different start/end dates (and those may differ from year to year, for example in 2007 the US dates changed).

This is based on the fact that the UK (well, London specifically) moves forward to 2 AM BST on the last Sunday of March at 1 AM local (or UTC), and back to 1 AM GMT the last Sunday of October at 2 AM local (1 AM UTC). Corroborate here; link courtesy @JackDouglas.


Use sp_WhoIsActive in AWS RDS (MSSQL) I'm trying to run Adam Machanic's sp_WhoIsActive in AWS RDS for SQL Server. When I run as the designated master account, say "TheAdmin", it works.  When I run as another user, say "AlmostAdmin", whom I've made a db_owner on the target DB, say "MyDB", it fails with permission errors like so:

Msg 297, Level 16, State 1, Line 49
The user does not have permission to perform this action.
Msg 297, Level 16, State 1, Line 342
The user does not have permission to perform this action.

Note that, because you can't create any objects in the master DB in RDS, I had to create sp_WhoIsActive in the user DB MyDB, as stated.

Anybody had any luck making this work in RDS?  Thanks!

Other things I've tried:

Granted VIEW SERVER STATE to "AlmostAdmin" and added him to server-roles processadmin and setupadmin to mirror "TheAdmin".
Added "AlmostAdmin" to all other elevated DB roles in MyDB (yeah, I know, shouldn't make a difference, just grasping at straws).

I guess I'll answer myself, since it's working...

The required permissions seem to be as follows:

"AlmostAdmin" is a SQL Login in the public server role, GRANTed securables "Connect SQL" and "View Server State".
The login is mapped to "AlmostAdmin" user in MyDB, which is in the db_owner role with default schema dbo.

My problem seems to have been with impersonation, because even though it fails for me when I run the following--

EXECUTE AS USER = 'AlmostAdmin'
EXEC sp_WhoIsActive
REVERT

--It works for the actual user when he's logged in himself (via SSMS).  And of course then I logged in as that user and verified that it was working for me too (when using that login, instead of my "TheAdmin" master login!).

Lesson learned: don't rely on impersonation 100% for testing security/permission issues; get the target user involved to test it out as well (or if you're in a position to know the credentials, lower yourself to that level and test it, instead of completely relying on EXECUTE AS).


How can I replicate MySQL data from many servers to one? All over the internet I can find manuals on how to replicate databases from master(s) to slaves.

What I need is to continuously keep a copy of table from 5 different servers in sync.

We have one application running at 5 locations. We need to have a copy of the system_log table from every one of those 5 servers always in sync on one new server at our 'headquarters'.

What is the best way to achieve this without using custom scripts?

Sorry.  If you are trying to have many Masters and one Slave -- that is not supported.  (Some third party addons may have such.)  It is better to have the 5 servers all write to the 'headquarters' Master.  Network latencies are probably not a serious problem.


Backing up databases on SQL Server hosted in Windows Azure VM One of the clients at my workplace asked me to find out information on what would be the best possible or recommended approach to run maintenance jobs such as backup job for a SQL Server 2016 hosted in Azure.

Should I be running native SQL backups and dumping the backup files in Azure blob storage, or do I need to ask the client to have a file share as a placeholder for the backup or is a locally attached disk better?

The various options are all well documented here, including a decision matrix to help you decide.


locked records progress4gl I have a query that runs from the sql engine in a progress DB. My problem is when a table or a record is locked from the progress4gl engine, I can't access it from sql engine and the query returns nothing for that lock.
Is there any way to access those rows or tables from a query in sql?

I've been searching and i found one solution for this problem, the only one thing that i do was put "WITH (NOLOCK)" at the end of the query, something like this:
"SELECT * FROM table1 WITH (NOLOCK)", with that statement the query is able to read the rows locked.

I hope i was clear and that this can be useful for someone

Regards


Max Degree Of Parallelism What is the Best Practices for Enabling and Disabling the MaxDop Setting for SQL Server on regular basis. Is it ok to create a job and run the job as required or have the script stored as SP?

This link is probably a good place to start.

Setting the MAXDOP at the server level depends on a lot of things like whether you're using a virtual or physical server, how many physical cores you have, hyperthreading being enabled or not and the number of NUMA nodes you have.  But you if you tinker with that, you should be made aware of increasing your cost threshold for parallelism to a much higher number than the default (5).  This will take a bit of research as to determine at what subtree cost your server typically handles under no parallelism (MAXDOP 1).


How to restore data after MySQL Upgrade? I upgraded from MySQL 5.5 to 5.6 by uninstalling 5.5 and then installing MySQL 5.6. Now that MySQL 5.6 is running, none of my DBs or data is present/visible in MySQL Workbench.

I also see that I have two data directories now:

C:\ProgramData\MySQL\MySQL Server 5.5\data
C:\ProgramData\MySQL\MySQL Server 5.6\data

I've tried various things with no luck. I'd really appreciate someone pointing me in the right direction.

Open up an Command Line Window as Administrator. Then, follow these steps

STEP 01 : Shutdown MySQL

net stop mysql

STEP 02 : Get the MySQL 5.5 Data into the MySQL 5.6 Data Folder

cd "C:\ProgramData\MySQL\MySQL Server 5.6"
move data data_empty
mkdir data
cd "C:\ProgramData\MySQL\MySQL Server 5.6\data"
xcopy /s "C:\ProgramData\MySQL\MySQL Server 5.5\data\*.*" .

STEP 03 : Upgrade All MySQL 5.5 Files in the MySQL 5.6 Folder

cd "C:\ProgramData\MySQL\MySQL Server 5.6\bin"
mysql_upgrade --upgrade-system-tables

STEP 04: Startup MySQL

net start mysql

Review These Steps

Give it a Try !!!


Why does SSMS highlight SUBSTRING in purple but RIGHT in grey? I apologise for the trivial question, but I was trying to work out how to do a RIGHT in SQL so I experimentally typed RIGHT and it didn't highlight in purple, so I spent some time scratching my head about how to do this using substring and length, then I later discovered that RIGHT is perfectly valid.

How come it wasn't highlighted purple?

Because while you're right that RIGHT() is a function, RIGHT is also a keyword, as in RIGHT OUTER JOIN. They have to pick one (SSMS is not smart enough to detect context). I face the same problem in the syntax highlighting plugin I use for my blogs - it either has to color all instances of RIGHT as grey, or all instances of RIGHT as purple.

(Also, if you have doubts about T-SQL syntax, don't waste your time trying to rely on IntelliSense to guide you - consult the documentation, that's what it's for.)


SQL Server : I need to combine multiple rows into one row with different column values I have a view

Order          Drink       Entree         Desert
--------------------------------------------------
12             Null        Preparing      Null
12             Ready       NULL           NULL
12             NULL        NULL           Waiting

I want to convert it to:

Order          Drink       Entree         Desert
---------------------------------------------------
12             Ready       Preparing      Waiting

You can use an appropriate aggregation function. In this case, it seems like MIN or MAX will do the trick:

SELECT Order,
       MIN(Drink) Drink,
       MIN(Entree) Entree,
       MIN(Desert) Desert
FROM dbo.YourTable
GROUP BY Order;


I have a correct subquery that returns results but when I use it as an "IN" clause I get error 1064 This must be a very simple problem, the subquery returns results alone but the compound query reports:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table where table in (select table_name from information_schema.tables where tab' at line 1

Here's the query:
select *
from table
where table in
    (select table_name
     from information_schema.tables
     where table_schema='my_database'
     limit 1);

I have also tried it without the limit, that was my first attempt:
select *
from table
where table in
    (select table_name
     from information_schema.tables
     where table_schema='my_database');

You can't SELECT like this, where you dynamically SELECT the table name. That's why it's not working. The subselect works, but.. try to replace subselect by a constant (i.e. known) name and it still won't work.
For something like this you either need an if then else flow or dynamic SQL or do it on application side.


Full text index for a column that stores json which contains guid - Is this a good idea, space-wise? I'm considering adding a full text index for a column whose values are json strings. These jsons usually contain Guid (or UUID) values.
The index goal is to allow fast search over both "normal" words (e.g., some user generated text) and guids.
Some more info about this column, called RequestContent:

I actually have another column, ResponseContent, which the following applies for as well, so that I presume that an answer for this question would apply for it as well.

This column is essentially used as a log for HTTP Post requests' body, so that each incoming POST request body is logged into that column. There are hundreds of web services whose requests bodies are logged, and there is no relationship between two different web services' requests.
For example, one request could look like:
{ "orderId": "607bbc9b-9c0e-4921-b5c3-55f68891e619", "status": 0, "createdOn": 16011560804216 ... }

While another could look like:
{ "claimStatus": 1, "actionStatus": 8, "senderId": "807acc23-9dde-4aaa-b5c3-55df5431e619", "documents": [ { "documentTitle": "some free text" }] ... }

Any json may contain fields of any kind: number, short string fields (like firstName, or Guids, etc.) or long string fields (usually containing hundreds of words of free text, but sometimes even large Base64 encoded data), boolean, nested objects, arrays. If it matters, it would be safe to say that most requests (I'd say 80%) contain number and short string fields.
This column's type is ntext (yes, it's old), but I can recreate it as any other type that I'd like.
Is it a good idea to build a full text index for this column? My concern is that the index would contain a row for each unique Guid, which would result in consuming an unfeasible disk space.
EDIT:
@David Spillett asked:

Could you extract the parts that you want indexed into a persisted
computed column that you can then add a full text index for?

It's technically feasible, but I'm looking for an answer for the current use case.

... a full text index for a column whose values are json ... usually contain Guid (or UUID) values.
Is it a good idea to build a full text index for this column?

I would say "No".
A full text index makes all of the words in the field available for searching.  I don't think that's what you want.  You want to be able to search for those Guid/Uuid values and, possibly, specific Request Types.
My preference would be to pull these values out into separate fields in their own right and index them.
Failing that, use a JSON-aware index type, which will be better, but still slower than the separate fields.


Why are there no indexes contained in the information_schema database? For the information schema.processlist:

Why are there no indexes on any of the fields (for example, a pk on ID)? Is this something that is not written through 'via sql' and is only reading directly from the storage layer to show the stats, or why isn't this table (and all other information_schema tables) constructed like a normal relational table?
And from the top of the mysql docs about them:

The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name

Also the way the information schema tables is generated internally means that the entire table is generated before a single attempt at filtering is done on the table.
An index has no meaningful effect on information_schema tables.
So for performance considerations, don't overly use information_schema tables, there could be performance impacts on the entire system.
note: above refers to MySQL-5.7 and MariaDB information_schema. I haven't looked at the MySQL-8.0 implementatoin.


Improve AlwaysOn failover time I have set up an AlwaysOn with a primary and secondary replica on different subnets but I have been forced to use Registerallprovidersip set to 1.  This causes the listener to only have 0 IP address in DNS which means I am at the mercy of DNS replication when a failover event occurs as it will take sometime for the listner's IP to update.

Has anyone in a similar situation found ways to improve failover time?

You should modify the registerallprovidersip setting in conjunction with changing the time to live (TTL) for the records.

Without modifying the default TTL, the registration lives too long. Sadly there is still a delay but dramatically reducing the TTL helps.

Ideally you should see what you can do to get the client connections to specify multi subnet failover and not have to do this workaround. But with the TTL being lowered, you should be fine, I've done that with several clients and it all works out alright. It still won't instantaneous.

You can see more about these settings working in conjunction with each other here.


what is "killcursors: found 0 of 1" from mongodb.log with replicaset? This is output of mloginfo log --distinct. What does killcursors mean exactly and what's the DBA action? We use latest mongod in a 3 node replicaset.

With a search engine i found only on mongodb-user mailing list

  Killcursors is a database command that removes cursor references on
  the server. It's normal and harmless. You'll probably only get an
  exception when inserting in safe mode.

I wish more info.

    source: mongodb.log
       host: example
      start: 2016 Jul 15 07:35:28.419
        end: 2016 Aug 19 08:00:15.874
date format: iso8601-local
     length: 244025
     binary: mongod
    version: 3.2.8
    storage: wiredTiger

DISTINCT

   64062    connection accepted from ... # ... ( ... now open)
   63984    end connection ... ( ... now open)
       4    killcursors: found ... of
       3    assertion ... ns: ... query:
       1    git version:
       1    waiting for connections on port

Logs

2016-08-05T15:29:36.935+0200 I COMMAND  [conn39339] killcursors: found 0 of 1
2016-08-05T15:29:36.937+0200 I COMMAND  [conn39327] killcursors: found 0 of 1
2016-08-05T15:29:36.945+0200 I COMMAND  [conn39329] killcursors: found 0 of 1
2016-08-05T15:29:36.946+0200 I COMMAND  [conn39343] killcursors: found 0 of 1

2016-08-05T15:29:36.935+0200 I COMMAND  [conn39339] killcursors: found 0 of 1

As per the info you've found, this is a normal (and benign) message: no administrative action is required. This message may be logged when a client/driver issues the killcursors command (for example, when an operation reaches a cursor timeout or an active cursor is explicitly closed). Normally inactive cursors are automatically cleaned up by the mongod server without a client killcursor command.

If you wanted to investigate further you could ask your application developers if they are using timeouts, closing active cursors before the results are exhausted, or possibly calling the killcursors command explicitly. You could also check on the specific version of the driver they are using if the frequency of this message has changed significantly from past occurrences.

However, given only 4 occurrences in more than a month of log activity (according to your mloginfo output), I would not be concerned by these messages.


Moving a large MySql database(Over 15 GB) from server to server mysqldump I need to move a mysql database that is about 17 GB that is on a Cpanel/Centos based server that I do not have root access, to a Centos server that I have full root access.  I do have limited SSH on the Cpanel with mysqldump available(I think).

When doing a test database download from the Cpanel Mysql backup utility, the server ran out of ram and shut down.  I only did about 250 MB as a test for the download.

My choices seem to be:

Master/Slave replication(I don't have root so that seems out)
Rsync the database files(I don't have root so that seems out)
Mysqldump
PhpMyadmin(Crashes the server when the tables have too many rows)

At this point I am planning on doing a mysqldump from the Cpanel server.  I don't want to have it locked down for a super long time, since our website is live.  I saw that I can use the mysqldump with the -r and it will cause the buffer to go row by row instead of loading the whole table into the buffer.  Some of the tables have 2 million rows.  Most are under 50 thousand rows.

What is the best way to move the database without locking down the system or causing the server to crash because of the ram being overloaded?

Both servers have powerful CPU's and 16 GB of ram with fairly current MySql versions.

Would you recommend a table by table dump or is that even necessary?

I might be able to get the current Cpanel host to setup my.cnf for me to set up the master slave connection(Not sure about that one though)

What you need to do is mysqldump separate tables in batches and load them.

On your new CentOS server, write the following

MYSQL_SOURCE_HOST=10.20.30.40
MYSQL_SOURCE_USER=root
MYSQL_SOURCE_PASS=rootpassword
SRC_CONN="-h${MYSQL_SOURCE_HOST} -u${MYSQL_SOURCE_USER} -p${MYSQL_SOURCE_PASS}"

MYSQL_TARGET_HOST=localhost
MYSQL_TARGET_USER=root
MYSQL_TARGET_PASS=rootpassword
TGT_CONN="-h${MYSQL_TARGET_HOST} -u${MYSQL_TARGET_USER} -p${MYSQL_TARGET_PASS}"

SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema = 'xcart'"
SQL="${SQL} ORDER BY data_length"
mysql ${SRC_CONN} -ANe"${SQL}" > /tmp/ListOfTables.txt

COMMIT_COUNT=0
COMMIT_LIMIT=10
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers --events"
for TBL in `cat /tmp/ListOfTables.txt`
do
    mysqldump ${SRC_CONN} ${MYSQLDUMP_OPTIONS} xcart ${TBL} | mysql ${TGT_CONN} -Dxcart &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ] ; then wait ; fi

I have other ideas from my old post : How can I optimize a mysqldump of a large database?

Give it a Try !!!


Is this temp table behaviour documented? Query Language (SQL) and PL/pgSQL functions treat temp tables differently:

begin;
create table foo(id) as values (1);
select * from foo;
/*
 id
----
  1
*/
savepoint s;
create function f() returns setof integer language sql as $$
  create temporary table foo(id) on commit drop as values (2);
  select id from foo;
$$;
select * from f();
/*
 f
---
 1
*/
rollback to s;
create function f() returns setof integer language plpgsql as $$
begin
  create temporary table foo(id) on commit drop as values (2);
  return query select id from foo;
end;
$$;
select * from f();
/*
 f
---
 2
*/
rollback;

The docs say:

  ...Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names...

This does not mention the exception demonstrated above. Is this documented elsewhere?

This is because while executing the SQL function the entire function body is parsed and executed. But with the PLPGSQL function statement by statement is parsed and executed. Because of this reason the SQL function not able to see the temp table which is created during the function execution.

In other words this applies to other objects, not just temp tables. This is easily demonstrated too:

begin;
--
create function pg_temp.f() returns integer language sql as $$select 1;$$;
create function g() returns integer language sql as $g$
  create or replace function pg_temp.f() returns integer language sql as $$select 2;$$;
  select pg_temp.f();
$g$;
--
select g();
/*
 g
---
 1
*/
select g();
/*
 g
---
 2
 */
--
rollback;

or even:

begin;
create schema stack;
set search_path to stack;
create temp table foo(id) on commit drop as values (1);
select * from foo;
/*
 id
----
  1
*/
savepoint s;
create function f() returns setof integer language sql set search_path = stack, pg_temp as $$
  create table foo(id) as values (2);
  select id from foo;
$$;
select * from f();
/*
 f
---
 1
*/
rollback to s;
create function f() returns setof integer language plpgsql set search_path = stack, pg_temp as $$
begin
  create table foo(id) as values (2);
  return query select id from foo;
end;
$$;
select * from f();
/*
 f
---
 2
*/
rollback;

The suggestion on pgsql-bugs is that the documentation is amended to make this behaviour explicit.

edit:

A change to the documentation has apparently been comitted but isn't yet showing in the 9.4 docs