What is the best practice for selecting from two tables with multiple relations? I have two tables with a structure like below:

and am selecting the data with below query:

SELECT
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = DN) DN,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = PN) PN,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = Winder) Winder,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = CouplingType) CouplingType,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = Type) Type,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = ILayer) ILayer,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = OLayer) OLayer
FROM Product_Coupling_Serial

So,what is the best practice for the best performance of selects like that (fastest way)?

Create a covering index on Id column and Include Name on the table Management_EnumerableItem.

CREATE INDEX IDX_Name on Management_EnumerableItem (Id) Include (Name) with (fillfactor=90);

and write it like this.

SELECT A.NAME,
B.NAME,
C.NAME,
D.NAME,
E.NAME,
F.NAME,
G.NAME,

FROM Product_Coupling_Serial P
LEFT OUTER JOIN Management_EnumerableItem A
ON A.ID = P.DN
LEFT OUTER JOIN Management_EnumerableItem B
ON B.ID = P.PN
LEFT OUTER JOIN Management_EnumerableItem C
ON C.ID = P.Winder
LEFT OUTER JOIN Management_EnumerableItem D
ON D.ID = P.CouplingType
LEFT OUTER JOIN Management_EnumerableItem E
ON E.ID = P.[Type]
LEFT OUTER JOIN Management_EnumerableItem F
ON F.ID = P.[ILayer]
LEFT OUTER JOIN Management_EnumerableItem G
ON G.ID = P.[OLayer]

Thanks


mysql export with partial data How do I export the structure of a mysql database I have, keeping the structure for all the tables but only exporting the data for some of the tables?
After exporting how do I import it into another mysql database on a different machine.

I can have the list of table names if it helps and I would prefer it if the solution will use command line but GUI is also acceptable (on windows).

Thanks

1. To dump only table structures

a. dump

mysqldump -d -u root -p"password" --all-databases > /tmp/dumpfile.sql

b. restore

mysql -u root -p "password" "dbname" < /tmp/dumpfile.sql

2. To dump only data not structure

a. dump

mysqldump -uroot -p"password" --no-create-info "Db" "TableName"> /tmp/dumpfile.sql

b. restore

mysql -u root -p password "dbname" < /tmp/dumpfile.sql

3. To dump inserts only for specific Columns

a.dump

mysqldump -t -uroot -p"pawword" "Db" "TableName" --where =”Columnname in (1,2)” > /tmp/dumpfile.sql

b. Restore

mysql -u root -p password "dbname" < /tmp/dumpfile


Custom index maintenance percentages for a very large table I'm using Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64)   Feb 15 2020 01:47:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: )
I have some very large tables (the tables by themselves can be more than 700gb) on a database that's 2.5 terabytes.
I've been using some scripts for index maintenance.
Right now I'm doing :

Reorg if fragmentation is > 10%
Rebuild if fragmentation is > 50%.

It works fine most of the time but for my tables that are 700gb or larger, I think that 10% will be a hard percentage to hit. I was wondering if I should lower that percentage for those big tables or if it's fine to leave it at that threshold.
I have to add that my servers have SSD drivers, but only 128 gb of RAM because it's Standard edition. So there's no way that the tables can fit in RAM, in case of large queries (like reporting), it has to scan/seek the indexes.
I don't want to hit the 665 error file system limitation due to fragmentation.
https://docs.microsoft.com/en-ie/troubleshoot/sql/admin/1450-and-665-errors-running-dbcc
(I'm starting to add filegroups to the database to avoid that error, as I'm having it once in a while on those large tables that are part of the primary file group.)
So should I do a custom percentage for index maintenance on very large tables?
Or it should be fine?

It really depends on how transactional your tables are. If they are frequently being updated and inserted into, you'll definitely hit the 10%. If you're not hitting the 10% then it's probably not even worth it to lower to 5% because that means your tables are fairly well unfragmented already.
I've personally worked on a very similarly sized database to your example, also SQL Server 2016 standard, on a server that only had 16 GB of RAM. Our database was fairly transactional (adding about 1,000 new records every few minutes) and we were hitting the 10% threshold for reorgs every night. Because the operation was so heavy and took a while, I actually did the opposite and increased the fragmentation threshold for reorgs to 20% for our larger Tables' indexes. This proved a lot better for our business because the different in performance was negligible and it generally allowed for the larger tables to not be reorganized until the weekend.
Worrying about index reorgs, and even more so rebuilds, is on the low end of things to optimize for. Even Brent Ozar says not to worry about it. :)


how to verify that next sequence value can be used as a new primary key I have many tables with a trigger attached like this

BEFORE INSERT
ON BRULES
...snip...
  if inserting and :new.ID is NULL then
  SELECT BRULES_SEQ.nextval into :new.ID FROM DUAL;
  end if;

Due to an interesting coding style the application does inserts on some tables with a null ID which fires the trigger and uses the existing sequence.  For other tables it gets the ID of the newest record and adds 1.  In this case the sequence is not used and gets out of synch with the number of records in the table.

How can I tell if the next value of a sequence is valid for an insert?  I could read all triggers for the sequence name and then do some dynamic SQL to compare the maximum ID and the current value of the sequence but it seems a bit clunky.

The values of the PK comes from different sources: horrible. Using the sequence would be the preferred way. You can not tell what exactly is the next value for the sequence by lookin in the dictionary. There is caching in effect, obviously for performance reasons. If you look in the dictionary you will find the value that is valid after the cache ran out of numbers.
What you don't know, is how far the cache is used.

    drop sequence z;
    create sequence z;  -- default cache size 20
    select last_number from user_sequences where sequence_name = 'Z'; --> shows 1
    select z.nextval from dual; --> shows 1
    select last_number from user_sequences where sequence_name = 'Z'; --> shows 21

Can you ignore the pre entered value for the PK, so effectively only using the sequence? The way it works now is almost as bad as querying the max(pk)+1 from the table.


How can I alter the fill factor of an index outside of an ALTER INDEX command? I am using a COTS package to perform index rebuilds across my SQL Server portfolio. The package unfortunately doesn't have an option to set index fill factors globally for an individual instance or DB (I can set them when scheduling rebuilds on an index-by-index basis, but with 1,600+ indexes, I'd like to set them in advance in bulk). Outside of an ALTER INDEX command, is there a way I can redefine existing index fill factors?

Check your DB Server (or instance) options, it will set every index based on the fill factor you specified.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'fill factor', x --set the fill factor value here, such as 70 for 70%
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

edit:

Do note, it will not actually rebuild or alter existing indexes.  It will fulfill your request that all future index rebuilds will now have this fill factor, unless specified in the rebuild index command.  Anything specified in the rebuild command will override this option.


Oracle GIS database replacement My company has a huge (hundreds of millions of records) Oracle database of GIS/temporal information.  We are trying to switch to an open source alternative.

If it helps, we imagined the following requirements:

horizontal scaling (like elastic search, for example)
fully SQL compliant. We don't think we can use NoSQL, etc, because we would have to rewrite all our queries.
No license fee

No database is fully SQL compliant. But out of the SQL databases that provide any form of scaling you have PostgreSQL w/ PostGIS and MySQL to choose from.

PostGIS is the superior choice. For why see my comparison here.


do partitions make sense when dates are not involved? We are having some discussions about partitioning some application tables in SQL Server 2008 based on their quickly growing size.  One is an event table that is typically used for INSERTS (80% of the queries) or SELECTS that look for the primary key of the table (20% of the queries).  The other table is a "mapping" table that is all SELECTS looking up the primary key.

Would partitions along the primary key be helpful here?  I've done a lot of reading and all the classic examples of partitioning seem to be data warehouse tables that are partitioned on dates.  It also seems that partitioning can sometimes cause more harm than good.

What do you think about these tables?

You did not explain why you want to partition the table and what do you expect from partitioning. You only mention table size, which is hardly a criteria for partitioning. Performance wise partitioning will make everything slower, not faster. The best you can hope for is on-par performance with the unpartitioned table. Some scenarios that do make sense with partitioning are:

ETL needs like load jobs that manipulate data intensively in staging tables and then switch in the entire staging table in one fast operation
need to remove large volumes of data that has past retention period (monthly switch out and truncate)
administrative reasons, like the need to rebuild individual partitions

Many quote reasons like 'move old data to slower disks' but I don't buy that argument much. And another oft quoted reason is to distribute data in multiple files, but that is incorrect thinking as a filegroup can contain multiple files across many volumes and the engine would distribute IO across them anyway w/o any partitioning need.

You mention that your developers quote a case when partitioning by data increased performance. Perhaps is a case of a time series data that was clustered by id, not by date and in which all range queries (typical for time series) had to do table scans. Partitioning appeared to help because partition elimination reduced the amount of data scanned. But a proper clustered index would had addressed the problem much better (no surprise there, indexing is the usually correct answer to query performance issues).

An extreme case when partitioning is useful is hash partitioning to help spread insert last page latch contention.

But when push come to shove the partitioning is an 'all-or-nothing' approach that has a very heavy impact (consider that you can no longer have an unique primary key that does not include the partitioning field anymore) and percolates through the data model everywhere (eg. a lot of foreign keys have to be re-designed). It requires careful administration. Query Optimizer can misfire some horrible plans in presence of partitioning.

Kendra Little has a nice article explaining pros and cons: How To Decide if You Should Use Table Partitioning


ORA-12541 error while connecting to oracle express edition 11g My oracle express edition was working some months earlier.

I dont know what i changed in the system, now I could not connect to it. I tried logging in from sqlplus as sysdba and altered the accounts to unlock, grant connect and resource privileges but I couldn't connect to database through sqltool.

I checked the listener.ora file where the host was Rajan. Ping on Rajan is working(pointing to localhost).

TNS-12541: TNS:no listener

Error message is self-explanatory, the listener is not running, so start the listener with "lsnrctl start".
Since you are on Windows, it runs as a service, so you can start the service itself.


Can I share a MySQL database among multiple users exclusively? This can be a silly/invalid question but I am going to ask it anyway. I want to setup a testing environment where many developers can run their functional test against a single common database.

Now, how can I ensure that the developers don't mess up with the test data when they run their test simultaneously? Is there a way to partition the db based on the user accessing it? Partitioning may be the wrong word here but I think you get what I mean.

Set up a DATABASE of the shared data.  GRANT users only SELECT permissions.  (And maybe a few other non-destructive privs.)

Set up another DATABASE for each user.  GRANT ALL PRIVILEGES TO user1_db.* ON 'user1'@...;

Then set up a way to initialize their db with a copy of the readonly copy.  Perhaps it could be a Stored Proc.  Or maybe a shell script.

Another approach is to set up a Docker instances, one per user.  Again, there needs to be a script to initialize their data.

OR...

If each dev does not need to mess with the existing data, consider having a single DATABASE with permissions GRANTed at the TABLE level.


SQL Server evaluates where condition before or during the run? I have a Stored Procedured that looks similar to this:

CREATE PROC Campaign.STP_CampaignTransaction_Get
(
    @id             INT
    @first_name     VARCHAR(50)
    @last_name      VARCHAR(50)
    ,@age           INT
    ,@gender        INT
)
AS
BEGIN
    SELECT
        [id]
        ,[first_name]
        ,[last_name]
        ,[age]
        ,[gender]
    FROM dbo.Persons p
    WHERE   (@id IS NULL OR t.id=@id)
        AND (@first_name IS NULL OR t.first_name= @first_name)
        AND (@last_nameIS NULL OR t.last_name = @last_name)
        AND (@age IS NULL OR t.age=@age)
        AND (@gender IS NULL OR t.gender=@gender)
END

Basically as you can see all the parameters they work as filters. Lets say that I send only the @first_name parameter the performance will be the same as checking the parameters once and then running the following query:

SELECT
            [id]
            ,[first_name]
            ,[last_name]
            ,[age]
            ,[gender]
        FROM dbo.Persons p
        WHERE   t.first_name= @first_name

Or for each row sql will evaluate if my parameters are null or not and go through all the conditions?

NOTE: The question started with a performance discussion in my company that if we should create 2 STPs or only one with filters

As your procedure is written, you cannot pass only the @first_name parameter, since the other parameters has no default value. But I assume you meant them to have NULL as default, or that you will pass NULL for them.

There is nothing special about NULL. SQL Server will create a plan based on the values for the parameters (aka Parameter Sniffing). That plan will then be cached for re-use. I.e., the plan might be OK for when you search for precisely firstname. But it can be a disaster for the next execution, when you search for lastname.

Adding OPTION(RECOMPILE) will allow a "perfect" plan for each execution, but at the cost of creating a plan for each execution.

Or you can use sub-procedures, executing a sub-proc conditionally based on the parameters passed (which is not the same as doing IF and have several queries in the STP_CampaignTransaction_Get procedure!).

Or use dynamic SQL and construct precisely the SQL you need (only include the WHERE condition(s) needed) and then execute precisely that SQL. I.e., you will have different query plans cached, based on the combo of search arguments.

Erland explains this very well in his article, the one that Bijy refers to in his comment, so go check it out!


Adding a data file to existing sql server database taking too long I am trying to add file using below

USE [master]
GO
ALTER DATABASE [foo] ADD FILE ( NAME = N'foo_1', FILENAME = N'D:\Data10\Data\foo10.ndf' , SIZE = 102400000KB , FILEGROWTH = 0) TO FILEGROUP [PRIMARY]
GO

Actually i have to add 2 TB file to this 3 TB disk, but trying to add in chunks of 100 GB

But it seems to be running for past 30 mins

HOw can i improve this?

Update:

The Query above completed in 40 mins. The wait was ASYNC_IO_COMPLETION

Also, this database is 24*7 and has DB mirroring configured.

Now, i am trying to increase in 50 GB chunks using below

USE [master]
GO
ALTER DATABASE [foo] MODIFY FILE ( NAME = N'foo_10', SIZE = 153600000KB )
GO

Above seems to be running for past 10 mins with wait (183043ms)PREEMPTIVE_OS_WRITEFILEGATHER

I have a feeling something is iffy about your setup here.

If you run the following script:

DBCC TRACEON(3004, 3605, -1);
GO

CREATE DATABASE Crap;
GO

EXEC sys.sp_readerrorlog;
GO

DROP DATABASE Crap;
GO

You should see this in the error log:

In other words, only the transaction log needs to be zeroed out. The data file should skip this.

I can run this commands and have it finish instantly as well:

ALTER DATABASE Crap
    ADD FILE ( NAME = N'Crap_1', FILENAME = N'F:\SQL\Data\Crap_1.ndf', SIZE = 102400000KB, FILEGROWTH = 0 )
    TO FILEGROUP [PRIMARY];
GO

If you're on a new enough version of SQL Server (as your question >implies), you can see if this query helps:

SELECT servicename, instant_file_initialization_enabled
FROM   sys.dm_server_services;

If that doesn't work, you'll have to go back in time and use whoami to determine that.

Keep in mind that your server needs to be restarted to enable IFI, so if you just added the priv, or you added it and never restarted SQL, it won't be in effect.

  Also, this database is 24*7 and has DB mirroring configured

If this is sync mirroring, perhaps IFI isn't enabled on the parter? Have you checked to see what's up over there?


How to verify SSL always being used on Postgresql 9.6 I have a sensitive application with app server and db on separate machines, and in the case of the slave db, in separate data-centers.

Although I believe my postgresqls are configured to always use ssl I need a way to double-check this.

Is there some simple way to check that all client connections are indeed being forced to use ssl?

Non-SSL connections can be disabled through pg_hba.conf.

For instance, it may start like this:

# allow local connections through Unix domain sockets
local  all  all  peer

# allow non-encrypted local TCP connections with passwords
host       all  all  127.0.0.1/32   md5
host       all  all  ::1/128        md5

# reject any other non-encrypted TCP connection
hostnossl  all  all  0.0.0.0/0     reject
hostnossl  all  all  ::/0          reject

# other rules...

The rules are tested in order and until the first match, so any rule after these will have no effect when one of these matches.

At runtime, to check which sessions are encrypted, there's the pg_stat_ssl system view  (since PostgreSQL 9.5). Its pid column is a reference to  pg_stat_activity that holds the other bits of information that might be relevant to identifying the connection such  as usename, datname, client_addr..., so you might use this query, for instance:

SELECT datname,usename, ssl, client_addr
  FROM pg_stat_ssl
  JOIN pg_stat_activity
    ON pg_stat_ssl.pid = pg_stat_activity.pid;


An exception occurred while opening a connection to the server I've encountered this from time to time and I cant seem to find the fix.
Usually when this error occur, I am seeing increasing number of connections being used while its connection is not being disconnected.
Here is a sample logs on mongo.

But I observed that this log below is its normal behaviour:

Another thing that I noticed before the error occur is that there are numerous "Unable to gather storage statistics for a slow operation due to lock aquire timeout"
At the first time I encountered this issue. We set MaxConnectionPoolSize = 200; then it occurred again. Any idea how to fix this?

I guess it is a locking problem. You should open profiler and watch which queries are locking each other.
Otherwise may be you can try it after researches.
Document.
db.adminCommand( { setParameter: 1, maxTransactionLockRequestTimeoutMillis: 200 } )


Mysql replication dump without down time? empty sql file I try to create dump as replication master data and use followed command
 to create dump, but sql file only contains paramters and there is no data and structure.
The command is :

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > bckp.sql.gz

Contents of sql file is:
-- MySQL dump 10.13  Distrib 5.1.41, for debian-linux-gnu (x86_64)
--
-- Host: xxx.xxx.xxx.xxx    Database: XXX
-- ------------------------------------------------------
-- Server version       5.1.72-0ubuntu0.10.04.1-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2013-12-26  8:56:27

Why does this problem occur?

I found the problem. The problem is --flush-log option. I don't know why this causes the problem, but mysqldump works correctly after I remove the option.


SQL Server: backup error 112: not enough space on the disk - true or not? Last night my weekly backup on SQL Server 2014, running on Windows Server 2012 R2, failed with the following error in the log:

  failed with the following error: "A nonrecoverable I/O error occurred on file "D:\MSSQL\MSSQL12.MSSQLSERVER\Backup\backup-xxxx.bak:" 112(There is not enough space on the disk.).

When I checked disk space I found there was still 1 GB free, and the .bak file looked ok, all 4GB of it, not compressed.

So I ran a RESTORE VERIFYONLY command on that file:

RESTORE VERIFYONLY FROM DISK = 'D:\MSSQL\MSSQL12.MSSQLSERVER\Backup\backup_xxxx.bak'

that responded:

  The backup set on file 1 is valid.

So which version of the story should I trust?

Note: According to Pinal Dave (http://blog.sqlauthority.com/2016/04/14/sql-server-backup-randomly-failing-error-112-not-enough-space-disk/) the first error is not accurate, especially if using compressed backups.

Ok, my bad. :-/  SQL Server is correct on both accounts.

The weekly full backup starts at 6 PM on Sundays, and the daily incremental backup starts at 8 PM. The weekly backup is still running when the daily incremental starts. The daily incremental records a .bak file that is the same size as the full, because ?? all indexes, statistics and so on have changed.

At this point, or rather, some two hours later, when it has finished reorganizing indexes and updating statistics, there isn't enough disk left for the full backup.

The incremental backup that I checked with RESTORE VERIFYONLY is ok.
The full backup was never created.

So now I have to choose between some options:

start the Full backup a few hours earlier,
skip the incremental on Sundays,
(suggestions welcome ... )


What does "ERROR: failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits"" mean? After being bitten by the bug mentioned in the release notes of 9.3.4, I have realized that a very small part of my data is corrupt, after doing a failover. The bug showed it self as duplicate rows, with the same primary key.
Now I can easily afford to delete some data from my database if that’s what it takes to fix my problem, so I just deleted those duplicates whole hug. After doing this, I decided to write a script that drops/recreates all constraints (Pkeys and Fkeys), in order to validate that no more of my data is corrupt. This worked well, until I bumped into the following error, while creating one of the foreign keys:

ERROR:  failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits"
CONTEXT:  SQL statement "ALTER TABLE "pm"."fruits" ADD CONSTRAINT "fruits_pkey" PRIMARY KEY (id)"

What is it, and how do I solve it?

This error message is new in postgres and is indeed related to the replication bug fixed in 9.3.3

As mentioned in the git commit from Mon, 17 Mar 2014:

  The recently-fixed bug in WAL replay could result in not finding a
  parent tuple for a heap-only tuple. The existing code would either
  Assert or generate an invalid index entry, neither of which is
  desirable.  Throw a regular error instead.

Since the parent tuple is an internal notion of postgres, the implication would be that the data structure for this relation is in a corrupted state, beyond troubleshooting at the SQL level.

The safest way out of this mess would be, if at all possible, to pg_dump the affected databases, then drop and reload them, and then recreate the constraints on this clean state.


Query to report disk space allocation and used space We are using 6 databases in total for an application, and we can only share 4TB of space among all 6 auto-grow databases (via SAN storage).

I'd like to write a query (report) for a single database indicating the "Currently allocated space" and "Available free space" attributes under the Tasks > Shrink > Database option in SQL Server Management Studio.

Then I'd like to convert those numbers to TB and total each database to get a rough estimate of how much space we have left.  Can these fields be accessed via a T-SQL query?  If so, what would the query look like?

Here is the query Management Studio uses to populate those numbers:

SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
   WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
SUM(a.total_pages) AS [SpaceUsed],
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
   WHERE df.type in (1, 3)) AS [LogSize]
FROM
sys.partitions p join sys.allocation_units a
  on p.partition_id = a.container_id
left join sys.internal_tables it
  on p.object_id = it.object_id

You need to perform math here just like Management Studio does in order to get the same numbers. Also, the left join to sys.internal_tables seems superfluous at best. So adjusting that query to match your ideal output:

SELECT
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
   FROM sys.database_files AS df
   WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed],
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
   FROM sys.database_files AS df
   WHERE df.type in (1, 3)) AS [LogSize]
FROM sys.partitions p join sys.allocation_units a
  on p.partition_id = a.container_id;

With this update, assuming your database doesn't change in the meantime, it should yield:

753475.94    744030.07    2900.00

Doing some simple math, and isolating just the three numbers you want:

;WITH t(s) AS
(
  SELECT CONVERT(DECIMAL(18,2), SUM(size)*8/1024.0)
   FROM sys.database_files
   WHERE [type] % 2 = 0
),
d(s) AS
(
  SELECT CONVERT(DECIMAL(18,2), SUM(total_pages)*8/1024.0)
   FROM sys.partitions AS p
   INNER JOIN sys.allocation_units AS a
   ON p.[partition_id] = a.container_id
)
SELECT
  Allocated_Space = t.s,
  Available_Space = t.s - d.s,
  [Available_%] = CONVERT(DECIMAL(5,2), (t.s - d.s)*100.0/t.s)
FROM t CROSS APPLY d;


CMS (Central Manamenent Server) help/guidance? We have just recently come across the Central Management Server feature in SQL2008.
We currently have a number of environments and think that this feature could really help us in running multiple instances queries.
But before making the move to implement this to our production environment, would anyone with experience please help us with the following points?

Are there any best practices you recommend?
Are there any drawbacks/cons to consider if we decide to use the feature?
Any negative in creating a group consisting of both 2005 and 2008 servers?
And in regards to transactions, is this per instance or a whole group?

any reply would be very much appreciated.

There is no real best practice, but if you want to use a job server as well you should choose your CMS as a server which is a standard edition, if not you can even use a SQL express edition as your CMS.

If you are managing a multi server/instance environment, there are no real cons/drawbacks, this will enhance the way you can monitor & query your environment.Also if you have multiple dba's in your environment you can work together on the same CMS, and see the newly installed instances from your Collegue.

You can build your groups the way you like and want, I have environments where I have 2005 & 2008 & 2012 & 2014 together because they are production. The only thing that is a drawback with that is that some dmv's are different in 2005 then in 2008/2012/2014. So some multiserver-queries will fail if you don't make them 2005 compatible.

This is a transaction per instance, the result is returned as one result, but it will be a transaction for every instance.

I hope this answered all your questions.


Transactional Replication - table does not have the identity property I have a one way transactional replication set up between two 2005 MS Sql servers.  Recently I have started to receive the following message in the replication monitor from the subscriber:

  Table 'dbo.tblname' does not have the identity property. Cannot perform SET operation. (Source: MSSQLServer, Error number: 8106)

Will adding the identity column to 'dbo.tblname' on the subscriber resolve this error or do I need to drop and re-add the subscription?

There is no need to add an identity column for no reason.

My guess is someone is issuing a SET IDENTITY_INSERT ON when there is no identity column in this table.

You need to check the code to make sure it is correct.


What is difference between JOIN and simple query I have two tables in database one is Orders and another one is customers

Orders Table Contains : OrderID , CustomerID and OrderDate

Customers Table Contains: CustomerID , CustomerName , ContactName  and country

  Actually the "CustomerID" column in the "Orders" table refers to the
  "CustomerID" in the "Customers" table. The relationship between the
  two tables above is the "CustomerID" column.

Query with Join

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
    FROM Orders
    INNER JOIN Customers
    ON Orders.CustomerID=Customers.CustomerID;

Simple Query

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders,Customers
WHERE Orders.CustomerID=Customers.CustomerID;

Result of Both Queries is : OrderID , CustomerName and OrderDate

On both the above query i get the same result . I want to know the difference between these queries and if we can do it via SIMPLE query why use JOIN?

There is no difference: your two examples are completely equivalent but using different versions of SQL syntax. The database engine will handle them in exactly the same way.

Your first example is using an explicit join and is the preferred syntax these days. It was introduced in the SQL-92 standard and is supported by pretty much every SQL-style query engine.

Your second example is usually referred to as an implicit join. It is an older syntax but still supported by most SQL based query engines and is still in the standard (so is considered to be correct even though the newer syntax is preferred). It quickly fell out of favour with the introduction and wide support of explicit joins as these are usually clearer and easier to read & maintain (though the older syntax could be said to have the advantage of being more concise).

See https://en.wikipedia.org/wiki/Join_(SQL) for further examples of this and other join types, including some engine specific variations.


wordpress table is crashed and should be repaired I run a wordprees website and use a managed VPS server with cPanel/WHM. Got bellow error log and check the table. I repaired the table with repair table command from phpmyadmin. But this problem occur repeatedly.

131121 21:58:16 [ERROR] /usr/sbin/mysqld: Table './HIDDEN_wrdp2/wp_options' is marked as crashed and should be repaired
HIDDDEN_wrdp2.wp_options info : Found block that points outside data file at 7534168
info : Found block that points outside data file at 7534212
warning : Duplicate key for record at 1150328 against record at 520756
warning : Number of rows changed from 25466 to 25463
status : OK

And I frequently find a screen showing error establishing database connection.

What should I do now? Can anyone help me to solve this issue?

It seems that you have network connectivity issues.

Assuming the SAN of your VPS is connected using the same network (very likely), the database server could lose connection to its drives intermittently. That can cause all kinds of consistency issues within the databases.

As this is a hosted environment, you cannot diagnose or fix network issues yourself. Talk to your hosting provider and have them look at it.


Efficiently query MAX over multiple ranges When performing a MIN() or MAX() over a single range covered by an appropriately sorted index, SQL Server does a TOP() and so returns the value after fetching just one row.  When the search criteria include more than one range, SQL Server instead grabs all the indexed values from both of the ranges and does a stream aggregate, which is far slower than performing a TOP() for each sub-value.

For example, assume a large number of orders per customer in a table like:

CREATE TABLE orders
(
  customer_id int,
  quantity int
)

Running this query:

SELECT MAX(quantity)
FROM orders
WHERE customer_id IN (1,2)

will result in a query that takes several times as long as if only one customer ID were specified.

What is the most efficient way to perform a query like the above?  Relatedly, if separate results were needed (i.e. GROUP BY customer_id), what would the best method be?

SQL Fiddle: http://sqlfiddle.com/#!3/ef0c6/1

Here's a solution using CROSS APPLY, which does the same TOP query for each customer_id:

SELECT MAX(b.MaxQuantity) AS quantity
  FROM
  (
    SELECT 1 AS customer_id UNION ALL
    SELECT 2
  ) a
  CROSS APPLY
  (
    SELECT TOP 1
      quantity AS MaxQuantity
      FROM orders o
      WHERE o.customer_id = a.customer_id
      ORDER BY quantity DESC
  ) b;

This does the same work as the UNION ALL-based query you wrote in the Fiddle; the difference is that the customer_id input is abstracted from the meat of the query, so it can easily be converted to use a table variable or table-valued parameter, which will result in a static query plan, which is important. This approach will work well for a small number of customer_id values, and simply removing the outer MAX will return the maximum for each customer. I don't believe there's a way to further optimize this query for a small number of customer_ids using these data structures (assuming the customer_ids are random, and not a range).

For a large number of customer_ids, it probably is cheaper to do the index scan and stream aggregate than many seeks. To get this going faster, you'd have to move to some kind of denormalized data structure. MAX isn't supported in an indexed view, so rolling your own mechanism is the only way to go, either in application logic or triggers. Depending on the read/write ratio on this table, that may or may not be faster than the above approach: you'd have to test it in your exact scenario.


Cannot start MySQL service on linux RHEL when pointing datadir to mounted ecryptfs folder I moved the data directory for MySQL to an ecryptfs encrypted folder.
When I run service mysqld start as root, the service will not start.

[Warning] Can't create test file /mysql/ip-172-31-29-184.lower-test
^G/usr/sbin/mysqld: Can't change dir to '/mysql/' (Errcode: 13 - Permission denied)

When it used to start before my encrypting the folder

ls -lZ /mysql outputs

drwx------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 mysql

now

ls -lZ /mysql outputs
drwx------. mysql mysql system_u:object_r:ecryptfs_t:s0  mysql

I think the issue is with how I mounted the encrypted directory.
mount -t ecryptfs /mysql /mysql

mount outputs

/mysql on /mysql type ecryptfs
 (rw,ecryptfs_sig=6860c372da7bc418,ecryptfs_cipher=aes,ecryptfs_key_bytes=16,ecryptfs_unlink_sigs)

I am running Red Hat Enterprise Linux Server release 6.6 (Santiago), MySQL 5.6.22

Any advice is appreciated. Thank you for your help in advance.

Make sure the MySQL server owner is MySQL:MySQL using chown -R mysql:mysql /mysql and the permission level is 755 for everything except my.cnf 644. chmod 644 my.cnf then execute the following commands after changing the information inside my.cnf to the correct one.

cd /mysql/bin
./mysqld --defaults-file=/mysql/my.cnf --user=mysql &

Edit to answer the comment below
To shutdown MySQL safely without killing the SID (or PID) you can use one of the following commands:

/usr/local/mysql/bin/mysqladmin -u root -p shutdown

or

sudo mysqld stop

note: in-case you can more than SID running you need to specify the port for it.


Is it possible to get a status from a concluded operation? In MongoDB, you have the command "db.currentOp()", that gives you statistics about the current operation which is running on the database.

Here is the output of the command:

> db.currentOp()
{
    "inprog" : [
        {
            "desc" : "conn1",
            "threadId" : "140534427899648",
            "connectionId" : 1,
            "client" : "127.0.0.1:37394",
            "appName" : "MongoDB Shell",
            "active" : true,
            "opid" : 10120364,
            "secs_running" : 0,
            "microsecs_running" : NumberLong(34),
            "op" : "command",
            "ns" : "admin.$cmd",
            "query" : {
                "currentOp" : 1
            },
            "numYields" : 0,
            "locks" : {

            },
            "waitingForLock" : false,
            "lockStats" : {

            }
        }
    ],

    "ok" : 1

}

Since that there is no operation running on MongoDB, the command gives a status about itself, and among the keys above, there is the key "opid" which stands for Operation ID (obviously) and a number: 10120364

Is it possible to obtain data from a previous operation 10120363 in MongoDB?

Yes and no. Depends on operation what was done and if you have opLog (your system is replica set; one node replica set is enough).

local.oplog.rs records all changes (insert, delete, update) but not queries.

But, you don't get status of that operation nor any performance data.


How does PostgreSQL decide which query to cancel during deadlock cleanup I know that PostgreSQL will detect and clear deadlock once it happen by cancelling one of the queries. How does PostgreSQL decide which one to cancel? Is this deterministic or there is some randomness in this? Will it cancel the same query always when situation is the same?

According to the documentation located at http://www.postgresql.org/docs/9.4/static/explicit-locking.html#LOCKING-DEADLOCKS (emphasis mine):

  PostgreSQL automatically detects deadlock situations and resolves them
  by aborting one of the transactions involved, allowing the other(s) to
  complete. (Exactly which transaction will be aborted is difficult to
  predict and should not be relied upon.)


How to import values of a JSON to PostgreSQL? I have a JSON file gugu.json that looks like this:

{
"key1": {
          "value1": "normal",
          "value2": [
                     "PogU"
                    ]
        },
"key2": ...
}

I want to put all the contents of the JSON file into the table

KEKW(value1 text, value2 text, ...).

I already tried many thing like this:

CREATE TABLE gugu(
 data json
);

\copy gugu FROM 'gugu.json';

SELECT * FROM json_populate_record(null::KEKW, (SELECT data->'key1' FROM gugu));

But as you can see I just don't know how I would get the "second level" of JSON in the table?

You need to unnest the first level, and the second level in two steps:

Use jsonb_each() instead:

select d.val ->> 'value1' as value1,
       d.val ->> 'value2' as value2
from gugu g
  cross join jsonb_each(g.data) as d(ky,val);

Online example


Using intermediate result in backend with node-postgres Firstly, I am using the node-postgres module to access database in a node application.
I have this SQL code that pulls up data from a table data of which is pulled from another table.
SELECT dwa_id
FROM _tasks2dwa
WHERE task_id IN
(
    SELECT task_id
    FROM _tasks
    WHERE onetsoc_code IN
    (
    SELECT onetsoc_code
    FROM _alternate_titles
    WHERE alternate_title
    ILIKE '%Beta Tester%'
    )
)

I would like to use this query like client.query(query)(Please refer to the docs).
I would like to use the intermediate result (the table that is comprised of values of task_id) in a backend application. However I also need the end result, the table that is comprised of values of dwa_id in that backend application. I have a solution which is writing a separate query for each result, which seems inefficient. Is there another more efficient approach to this problem?

You will need to store the intermediate result in a temporary table, but there is no way to avoid the two queries:
create temporary table temp_result
as
SELECT task_id
FROM _tasks
WHERE onetsoc_code IN
    (
    SELECT onetsoc_code
    FROM _alternate_titles
    WHERE alternate_title ILIKE '%Beta Tester%'
    );

Then, using the same (physical) connection to the database, you can run the query to retrieve the intermediate result:
select *
from temp_result;

And the actual result you want:
SELECT dwa_id
FROM _tasks2dwa
WHERE task_id IN (select task_id from temp_result);

If you only want (or get) each dwa_id only once, another option is to aggregate the task_ids into an array:
Something along the lines:
SELECT td.dwa_id, array_agg(t.task_id) as task_ids
FROM _tasks2dwa td
  JOIN _tasks t on td.task_id = t.task_id
WHERE t.onetsoc_code IN (SELECT alt.onetsoc_code
                         FROM _alternate_titles alt
                         WHERE alt.alternate_title ILIKE '%Beta Tester%'
GROUP BY td.dwa_id;


Why not create ColumnStore index on every single table? As ColumnStore index improves performance for data warehouse databases, and also since SQL 2104, it is updatable too, why not creating ColumnStore index on every single table?

Edit: In SQL 2014 we cannot have any other index if there is a CLUSTERED ColumnStore index. This limitation is eliminated in SQL 2016 so my question can be more relevant for SQL 2016.

In SQL Server 2016, if you have & processing over million of rows, the only thing that would not allow you to use Columnstore Indexes will be the current technology limitations, such as Replication on CCI, CLR, CDC or CT over CCI and so on ...
Otherwise this should be a default way to approach any modern Data Warehouse.


Is 3 minutes to join a 50 million table too much? I apologize if this is the wrong place to ask, but I really need some opinion on whether or not my query is good or not.

Basically, I need to get a count of old and invalid entries per group, which is identified by a value of NULL or simply a month old. The selected data is going to be stored in a smaller table called "statistics". PHP is used for a cron script.

Now, the problem is I'm still a novice so I can't tell if the query is fast or slow. To finish the following query, it takes roughly 3 minutes, which according to my boss is slow and needs to be at least 50% faster, but I myself am not sure if anything can be done to speed it up. "cat_01" table has 50 mil row, the others have less then 2000.

I did add indexes to all used columns, which lowered the time from 6 to 3 minutes.

The query I'm using is as follows (I used pseudonyms for tables and columns, so don't mind the logic):

SELECT
'cat_01' as category,
m.shop_id,
o.name,
count(m.shop_id) as total,
tr2.traffic,
IF(mm.bought IS NULL,0,mm.bought) as bought
FROM cat_01 m
JOIN shops o
ON m.shop_id = o.id
JOIN (
    select
    mmm.shop_id,
    max(mmm.buy_date) as bought
    FROM cat_01 mmm
    GROUP BY mmm.shop_id
) mm
ON o.id = mm.shop_id
LEFT JOIN (
    SELECT tr.shop_id, sum(tr.sales) AS traffic
    FROM (
        SELECT
        mmmmm.shop_id,
        bs.sales,
        bs.order_id,
        bs.id
        FROM cat_01 mmmmm
        JOIN orders bs
        ON mmmmm.order = bs.order_id
    ) tr group by tr.shop_id
) tr2
ON tr2.shop_id = mm.shop_id
WHERE (m.buy_date IS NULL)
OR (m.buy_date <  UNIX_TIMESTAMP())
GROUP BY m.shop_id

Create table "cat01":

CREATE TABLE `cat_01` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `shop_id` int(11) DEFAULT '0',
 `buy_date` int(11) DEFAULT NULL,
 `order` int(11) DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `shop_id` (`shop_id`),
 KEY `buy_date` (`buy_date`),
 KEY `order` (`order`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Create table "shops":

CREATE TABLE `shops` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

create table "orders":

CREATE TABLE `orders` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `order_id` int(11) NOT NULL,
 `sales` int(11) NOT NULL DEFAULT '0',
 `status` tinyint(1) NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`),
 KEY `order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here is the explain (I don't have access to the live data from home, so I used dummy rows, but rest is the same)

EDIT: added UNIX_TIMESTAMP() as an example

EDIT2: I ended up removing the subqueries from the SELECT and used them seperately, then using a PHP loop, I created the appropriate INSERT query by mixing the results together.

P.S. I can't seem to find a direct example of how sql code formatting on sctackechange looks, so I apologize if it ends up garbled, I'll try to fix it shortly.

I do have an EXPLAIN result but not sure how to format that, might end up posting an image later.

If you need some particular info, feel free to ask, I myself am not sure what I should be looking at.

First, add these indexes:

ALTER TABLE cat_01
ADD INDEX `ShopBuyDates` (`shop_id`,`buy_date`)

ALTER TABLE orders
ADD INDEX `OrderSales` (`order_id`,`sales`)

Then try this query, and report the results. Make sure to run it at least twice, and discard the first result's performance, to flush out the effects of populating the cache.

SELECT
'cat_01' as category,
m.shop_id,
o.name,
count(m.shop_id) as total,
tr2.traffic,
IF(mm.bought IS NULL,0,mm.bought) as bought
FROM cat_01 m
JOIN shops o
ON m.shop_id = o.id
JOIN (
    select
    mmm.shop_id,
    max(mmm.buy_date) as bought
    FROM cat_01 mmm
    GROUP BY mmm.shop_id
) mm
ON o.id = mm.shop_id
LEFT JOIN (
    SELECT mmmmm.shop_id, sum(bs.sales) AS traffic
    FROM cat_01 mmmmm
    JOIN orders bs
    ON mmmmm.order = bs.order_id
    group by mmmmm.shop_id
) tr2
ON tr2.shop_id = mm.shop_id
WHERE (m.buy_date IS NULL)
OR (m.buy_date = 0)
OR (m.buy_date < 1)
GROUP BY m.shop_id

After we see the improvement from this (a new EXPLAIN would be good too, from this query), we can iterate to see what else is slowing down your query.


Multiple "root" users in MySQL Mysql is showing 5 root users:

| root   | %         |
| root   | 127.0.0.1 |
| root   | ::1       |
| root   | localhost |
| root   | mysrverhostname |

What are these 5 for? Are there some I can delete?

| root   | %         | - This is the root user from any computer
| root   | 127.0.0.1 | - This is the root user for localhost without resolved IP for IPv4
| root   | ::1       | - This is the root user for localhost without resolved IP for IPv6
| root   | localhost | - This is the root user for localhost with resolved IP
| root   | mysrverhostname | - This is the root user if you connect from the localhost to its externel IP.

You could the delete the first one if you don't like to connect from another computer to this server as root.

All other root users are useful, because you can do the follow types of mysql connections:

mysql -h 127.0.0.1 -u root (IPv4)
mysql -h ::1 -u root (IPv6)
mysql -h localhost (If the mysql server is configured to resolve IPs, the the 2 above would get used)
mysql -h 192.168.0.1 -u root (the IP of the server)
mysql -h mysqlserver.local -u root (the hostname of the server)

and if I remember correctly the "localhost" user get used if you connect using a socket connection.


SQL Server 2012 Transaction Log needed to create database mirror? There's a somewhat lengthy discussion of Transaction Logs in Microsoft's instructions for setting up a database mirror.  We've never bothered with Transaction Logs in the past; do we really need to now?

Isn't the Transaction Log effectively an incremental backup that follows a full backup?

Unless the database drive fails while building the mirror, won't the mirror get from the primary all of the events that occurred while the backup file was being copied and restored to the mirror server?

It seems to me that the full backup alone might be slower, but it would be simpler.

Additional information

When building a mirror, Microsoft's documentation instructs us to restore the last full backup (plus the last differential, if there is one) and all subsequent Transaction Logs.

Unless one puts the database in single-user mode while building the mirror, there will be at least a small activity gap between the last data restored and the time the mirror goes live.

Will the mirror fill in that gap (as it does when it temporarily goes off-line)?

There's a caveat in the instructions if the database path is different on the mirror server: we cannot "add" TLs.  The activity gap could be quite big if one had to skip several TLs.

That's what I meant above: wouldn't it be simpler to ignore the Transaction Logs when building the mirror, and just let the mirror fill in the gap?

The short answer to your first question is yes, your database must be in full recovery mode in order to use the database mirroring feature:

  The database must use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, bulk operations are always fully logged for a mirrored database. For information about recovery models, see Recovery Models (SQL Server).

Prerequisites, Restrictions, and Recommendations for Database Mirroring

  In a database mirroring scenario, every update to a database, the principal database, is immediately reproduced in a separate, full copy of the database, the mirror database. The principal server instance sends each log record immediately to the mirror server instance which applies the incoming log records to the mirror database, continually rolling it forward. For more information, see Database Mirroring.

The Transaction Log (SQL Server)

To answer your second (and third) question - that is an over simplification but you could view it like that. I would highly recommend reading about the Transaction Log in more detail using the link I have provided above. The Transaction Log is used for much more than just synchronising two databases - it is also used to implement point-in-time recovery. Try to understand how the transaction log behaves under each of the different recovery modes - this will improve your understanding of why full logging is required in order to implement high-availability on SQL Server.

  The transaction log supports the following operations:

  Individual transaction recovery.
  Recovery of all incomplete transactions when SQL Server is started.
  Rolling a restored database, file, filegroup, or page forward to the point of failure.
  Supporting transactional replication.
  Supporting high availability and disaster recovery solutions: Always On availability groups, database mirroring, and log shipping.

EDIT

Based on the additional information that you added, the answer is no. You cannot ignore the transaction log when setting up database mirroring - the mirror database must be an exact copy of the source before you can start. Once it has been established, then yes it will fill in the gaps - but that only works after you have begun the mirror. I normally enable mirrors during non-business hours, do a full backup and take the database offline while I copy the backup to the mirror server.


Multiple SQL Server data files on same SAN disk I'm currently in the process of creating a new database, and have previously only ever used a single data file and a single log file. I've done some research online regarding the benefits of multiple data files, but I've found mixed opinions regarding the benefits of multiple data files on a single disk. So my question is: if the only disk available to me is a SAN disk would I see any performance benefits (such as multithreading/parallel processing) from splitting the data into 4 separate files? Would I be better of with a single file?

Also would there be any performance benefit of separating the indexes (and possibly an archive table) into their own filegroups on separate files on the same disk? Would this only provide administrative benefits?

Thanks!

Even multiple tempdb files on a single LUN should be a last resort compared to one tempdb per LUN.
If there are multiple database files on a LUN, and they are allowed to auto-grow, their contents will be interleaved in the filesystem and on the storage.  Hit them both heavily with read or write requests, and a situation known as IO weaving, or disk head thrashing will result.  Even though writes are generally cached, writes are not immune to io weaving, because eventually the writes need to destage from cache.
If tempdb is used heavily by query spill, there will be a lot of sequential write activity.  Put multiple tempdb on the same LUN, and there can be multiple streams of sequential write activity at different locations on the storage, all accessed through the same LUN queue.  Sequential reads such as a table scan introduced on top can encounter worse-than-random read latencies.


Group records with same value in one row informix I have 3 records in a table below called systables_growth:

tabname         aactn
total_size_kb   32
date_added      2019-08-26 13:00:00

tabname         aactn
total_size_kb   37
date_added      2019-08-27 13:00:00

tabname         aactn
total_size_kb   39
date_added      2019-08-28 13:00:00

SQL server layout:

tabname     total_size_kb   date_added
aactn       32              26/08/2019 13:00
aactn       37              27/08/2019 13:00
aactn       39              28/08/2019 13:00

I want to achieve the desired output below:

tabname                 aactn
2019-08-26 13:00:00     32
2019-08-27 13:00:00     37
2019-08-28 13:00:00     39

SQL server output required:

tabname 26/08/2019 13:00    27/08/2019 13:00    28/08/2019 13:00
aactn   32                  37                  39

Based on the above output wanted. I want to combine records with the same table name and use the date_added value as the column name. I want to try and achieve this using a case statement, but am struggling with the logic. Please note that this is for informix not sql server. I only provided the layout for sql server to make it perhaps easier to understand what I am trying to achieve.

You are actually looking for PIVOT TABLE which seems to not be possible in Informix (I think, just by googling it, I don't know Informix). The CASE workaround, presentet e.g. here would mean that you would need a special CASE for each date... which seems pretty ugly (but you can do it).

What I would recommend, is to query the table in the "tall format" (also called "wide") and do the pivoting (wide format) transformation in the programming language, from which you call the database from. For example, I use R programmiing language a lot, and in R, I use the dcast or acast or cast function from reshape2 library to reshape the data. Just google it for your language:

  how to make pivot table in [[whatever-programming-language-you-use]]

or

  convert long table to wide table in [[whatever-programming-language-you-use]]


Oracle 11g accessing data in Oracle 7 I am working on a project that involves converting production data from an Oracle 7 DB to our system, which use Oracle 11g. In our other projects, we use DB link to access the other system DB, which is usually Oracle 10g. Can we have similar solution for Oracle 7?

We need a solution that can minimize down time of system.

DB links are probably not going to work as a solution. Oracle7 is crazy old.

The question's facts are little limited. What types of objects (e.g., tables, stored procs, triggers, etc.) are involved? How much data? That said:

As it relates to table data, my solution would be to spool the data to text and access the text via external tables you setup in the 11g db.

In the 11g db, create the tables with the structure and referential integrity you require. It's been so long since I used Oracle7, so I don't think you can leverage DBMS_METADATA to obtain this DDL. You may have to get creative to reconstruct it.

Fill them with data as selects from the external tables.

Finally, index the new tables as desired.


ALTER SYSTEM DISCONNECT SESSION doesn't work Oracle 11g. The devs leave sessions connected overnight, sometimes certain cron jobs fail because of that. I am trying to kill all user sessions, but I can't.

I login as:

sqlplus system/[password] as sysdba

I do this to list the sessions:

select sid,serial#,program from gv$session where type='USER' and machine!='this-server';

And the list looks like this:

       SID    SERIAL# PROGRAM

10   7883 toad.exe
11  42365 toad.exe
77  22493 toad.exe

Then I do:

alter system disconnect session '10,7883' immediate;

And is says:

System altered.

But nothing happens. The session is still there. What am I doing wrong?

P.S.: I'm pretty new to Oracle, I only used MySQL before.

If even an ALTER SYSTEM KILL... does not remove the session after a bit, then the only thing you are left with is to kill it on the OS level.  In Linux you can get the command to kill the process as follows:

SELECT 'kill ' || ' -9 ' || p.spid
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid=:SID_To_Kill;

In Windows you can get the command to kill the thread as follows:

SELECT 'orakill ' || sys_context('USERENV', 'DB_NAME') || ' ' || p.spid
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid=:SID_To_Kill;

It might be simpler just to have cron bounce the database before running the jobs.


normalize this! allow user-defined ordering but fall back to default order We have vendors, and they can either be company-wide (office_id=null) or office-specific (office_id is set). Company-wide vendors have an order, this is managed through the Company admin:

TABLE vendor
 - vendor_id (pk)
 - office_id (fk, nullable)
 - name
 - order (int)
 - (etc.)

By default all offices inherit the company vendors and their order. But offices are allowed to manage the order of all vendors independently if they wish. If they arrange the order of their Vendors, we create records in vendor_order:

TABLE vendor_order
 - vendor_order_id (pk)
 - vendor_id (fk)
 - office_id (fk)
 - order (int)

We have this query that gets all the vendors for an office (both company and office-specific), and orders them, first by using vendor_order.order if it exists, then falling back to vendor.order. It works fine, but I think it smells a little funny:

-- GET ALL VENDORS FOR OFFICE IN CORRECT ORDER
SELECT `vendor`.`vendor_id`,
       `vendor`.`office_id`,
       `vendor`.`name`,
       `vendor`.`order`,
       Coalesce(`vendor_order`.`order`, `vendor`.`order`) AS `office_order`
FROM `vendor` LEFT OUTER JOIN `vendor_order`
    ON (`vendor`.`vendor_id` = `vendor_order`.`vendor_id` AND `vendor_order`.`office_id` = 2)
WHERE (`vendor`.`office_id` = 2 OR `vendor`.`office_id` IS NULL
    AND (`vendor_order`.`office_id` = 2 OR `vendor_order`.`vendor_order_id` IS NULL))
ORDER  BY `office_order` ASC

QUESTIONS

Can this schema be improved/normalized?
Should we just use vendor_order table for the company order too?

Can this schema be improved/normalized? Yes, it would be better if all the ordering data was located in the same place.

Should we just use vendor_order table for the company order too? That's a good idea. That would place all ordering information in the same table where it is much easier to work with and maintain. Of course, you would have to create an office entry that would stand for this default "company-wide" office. But that shouldn't be a problem.

You would have to rewrite the query, of course.

As order data would no longer be in the Vendor table, you would also have to join it to the Vendor_order table on that "company-wide" office then left join again on the actual office number.

I've provided a simple sqlfiddle example. Comment out the WHERE clause to see that all vendors are listed for all offices in the correct order.

Should we make a separate table for office vendors? Not necessary. There should be a table for offices, a table for vendors, and a table which defines the default ordering and any special ordering for specific offices. See the example.


Change root password of mysql database This could be a Rookie problem , but it seems that i am not able to solve it.

I have MySQL server installed on my windows machine(MySQL 5.5) under the Admin username root.
Some How i lost the password of MySQL and want to change it , i m going through this link in order to solve it.
under c:> mysql-init.txt is :

UPDATE MySQL.user SET Password=PASSWORD('root') WHERE User='root';
FLUSH PRIVILEGES;

and on command line

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld --init-file=C:\\mysql-init.txt

it give an message like 'c:\program' is not recognized as internal or external command.

So basically i am stuck at point 6 of MySQL documentation. any help ??


SQL Server 2008 service keeps crashing Problem
I have a Windows 7 Ultimate x64 machine running SQL Server 2008 SP3. After creating a local database, when trying to start the service it keeps stopping (crashing).
Research
I read a lot of material online, disabled Windows and Java updates that might cause conflicts, and shut down the SQL Server service on the local machine in order to find out if it was the problem - but it didn't help.
Went over to the Event Viewer and received the following logs:

Cannot create file 'c:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\temp_MS_AgentSigningCertificate_database.mdf' because it already exists. Change the file path or the file name, and retry the operation.
Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Question
I don't really understand what is the temp_MS_AgentSigningCertificate_database.mdf file and is SQL Server trying to upgrade/update itself?
How do I resolve this issue?
Additional information
This is the first installation on a fresh operating system, so it couldn't be from any previous installation attempt. I should use the latest SQL Server version, which is much much better (I'm already using it for other purposes), but this one is used as a part of my learning of penetration testing. I need to launch this specific version. I don't think a reinstall would solve my problem in that case.
I really don't have anything on this database yet; I just finished installing it. It is the first install on a fresh operating system (Windows 7 Enterprise x64 on a VMware virtual machine, but that shouldn't really matter).
The book I am reading is https://www.nostarch.com/pentesting. It was published in 2014, so it is considered to be up-to-date, yet still explicitly requires to install SQL Server 2008 as it has known vulnerabilities that we can exploit as a part of the learning process.

As suggested by Aaron Bertrand, renaming the files was just enough to solve this problem:

Save your backup files (or mdf/ldf files if you don't bother taking backups), and then obliterate that instance. Install a new instance (preferably of a newer version) and then restore/attach your databases to the new instance.
Try renaming that mdf file it's complaining about (and probably the corresponding ldf file, too). I haven't seen this issue before but it may take some trial and error for such an ancient, out-of-support version. Does your pen testing thing mention SQL Server 2008 (which could just indicate when it was written), or explicitly require it? Seems odd to require a version that is FIVE major versions ago, no longer supported, and quickly declining in use. I would double check that you even need to be dealing with this.


SQL-Server: What is a good way to aggregate on different columns within the same query? I'm using a SQL query to generate a count of row repeats. The SQL query looks like this:

SELECT
  Category,
  COUNT(Category) AS Count
FROM
  TableA
GROUP BY
  Category

Which results in a table that looks like this:

Category  | Count
typeA     | 4
typeB     | 6
typeC     | 1
etc       | 9

Is it possible, within the same query to have an aggregation of the Aggregated rows? I would like to sum the Count column to get a percentage of each Category's contribution to the total. I.e. I'd like to produce the following table:

Category  | Count  | Perc of Total
typeA     | 4      | result of (4/20)*100
typeB     | 6      | result of (6/20)*100
typeC     | 1      | result of (1/20)*100
etc       | 9      | result of (9/20)*100

You could also use window functions:

Select Category,
    Count(Category) As [Count],
    Count(Category)*100.0 / Sum(Count(Category)) Over () As Perc
  From TableA
  Group By Category;


What do the columns in SYS.USER$ represent? In the SYS.USER$ view, what do the CTIME, PTIME, and LTIME columns represent?

Where can I find Oracle's documentation for this and other system views?

Test cases below show:

CTIME is the date the user was created.
LTIME is the date the user was last locked. (Note that it doesn't get NULLed when you unlock the user).
PTIME is the date the password was last changed.
LCOUNT is the number of failed logins.

Here's the test code:

SQL> create user philtest identified by philtest;

User created.

SQL> alter session set nls_date_format='HH24:MI:SS DD/MM/YYYY';

Session altered.

SQL> select ctime,ltime,ptime from user$ where name = 'PHILTEST';

CTIME               LTIME               PTIME
------------------- ------------------- -------------------
14:21:51 26/04/2012                     14:21:51 26/04/2012

SQL> alter user philtest account lock;

User altered.

SQL> select ctime,ltime,ptime from user$ where name = 'PHILTEST';

CTIME               LTIME               PTIME
------------------- ------------------- -------------------
14:21:51 26/04/2012 14:23:15 26/04/2012 14:21:51 26/04/2012

SQL> alter user philtest identified by foofoo;

User altered.

SQL> select ctime,ltime,ptime from user$ where name = 'PHILTEST';

CTIME               LTIME               PTIME
------------------- ------------------- -------------------
14:21:51 26/04/2012 14:23:15 26/04/2012 14:27:47 26/04/2012

SQL>

This isn't documented in the Oracle docs, as it's an internal data dictionary view. However, the DBA_%/ALL_%/USER_% views that sit on top of the SYS.% views are documented here.

I often find that doing a DESCribe on one of the DBA_% views gives a good indication of what the underlying columns represent. The DBA_% views are often commented too.


special setup to create views in Oracle in default schema When creating views in Oracle, do you need special setup for a given user if the view and table in which the view is reading from are in two different schemas?  The user creating the view has dba privileges.

The following command throws the error below.

create or replace VIEW view_patient as select * from DATA.PATIENT_DIMENSION where PATIENT_NUM in (select PATIENT_NUM from patient_list);

Error:

Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.

DBA role alone is not enough.

CREATE VIEW

  The owner of the schema containing the view must have the privileges
  necessary to either select (READ or SELECT privilege), insert, update,
  or delete rows from all the tables or views on which the view is
  based. The owner must be granted these privileges directly, rather
  than through a role.


How does JOIN involving database across 2 servers store the intermediate data? In case of a SQL JOIN on 2 databases across servers.
For example:
Select *
From server1.db1.dbo.tbl1 a
Join server2.db2.dbo.tbl2 b
On a.common_id=b.common_id
Where a.some_col = 1000

Suppose this query is run on server 2 via SSMS, then where does the query processing work? For example:

Data for server1.db1.dbo.tbl1 needs fetching

Then data for server2.db2.dbo.tbl2 needs fetching

Then join to be performed.

Then a filter

Where (on which server) does each of the above operation happen?

As Dan points out, it's dependent on the execution plan that gets generated, as the processing can occur on either end (the remote server or local server which executed the query). Usually you can determine which server it was actually processed on by seeing if there's a Remote Query vs a Remote Scan operation in your query's execution plan.
A Remote Query usually means the query is sent to the remote server to be processed, and the predicates will be performed remotely, resulting in less data being return back locally.
A Remote Scan generally results in all the data from the remote entities (e.g. the entire tables) to be returned to the local server first, then the query is processed, predicates are applied, etc, on the local server.
Evidently, Remote Query is usually a more efficient operator to have in your query's execution plan as opposed to a Remote Scan operator.
By the way, this is a great website with a quick description of the different query plan operators.
I've seen a lot of adhoc answers on query re-writes to try and fix plans with Remote Scan and force a Remote Query operation instead. I'm not positive on exactly what the SQL Engine is doing under the hood when it chooses one over the other, but I have consistently seen that when your query uses the primary key (and more likely resultantly the clustered index) field of the remote entities, as part of the predicates, it results in a Remote Query operation.


Which domain to use for sql server developer My current company does not have a QA domain in a hosting data enter where all the data bases are located . I want to know that in order to use developer editions of sql server, should I be using it in a domain which is different from production servers ? Or can I name the QA servers with a different naming convention and still use them in the same domain where prod is present? We have 3 data centers and in all the 3, the QA servers are in the same domain as prod . Is this ok ?

I noticed that my current client does have a QA.xx domain, but there are no servers hosted in it. Please advice which is the way to go to test newer configurations and setups because I want to take advantage of the developer editions of sql server

The Dev license is not about where, but rather how the installation is used.  If you are not using it for Production, its fine.    Separating out by domain is great, but not required.


SQL Server Always On AG – Is a Passive Secondary Required for Automatic Failover? It is my understanding that for automatic failover to occur in an Always On AG environment, the replicas just need to be configured with "automatic failover," "synchronous commit" and the environment must have proper quorum (i.e. odd number of voting members).
However, according to the "SQL Server 2019 Always On" textbook by Peter A. Carter, for automatic failover to work, the secondary cannot be active and must be passive. Passive meaning the Readable Secondary setting is set to 'No' so it is unavailable for direct connections or read access, and obviously no backups can be done on them either.
Here is the exact statement from the textbook:

"Although we can change the value of Readable Secondary through the
GUI while at the same time configuring a replica for automatic
failover without error, this is simply a quirk of the wizard. In fact,
the replica is not accessible since active secondaries are not
supported when configured for automatic failover."

Source: Pro SQL Server 2019 Administration: A Guide for the Modern DBA
Here's a screenshot of the "quirk"

Note: According to the textbox, the Readonly Secondary should be set to 'No' if you want automatic failover to work. Thinking back, I don't recall a successful automatic failover occurring with this set up and I believe when an issue occurred, the AG was in a RESOLVING state. Note, we have third server for proper quorum.
I've searched through Microsoft's documentation to confirm whether or not the author's statement is true but have yet to find anything that explicitly states that the secondary replica must be passive for automatic failover to work. This Microsoft article (Failover and Failover Modes (Always On Availability Groups)) states the conditions required for automatic failover but does not mention needing a passive secondary replica.
So is the author correct and Microsoft has failed to advertise this OR is the author incorrect and automatic failover does work when the secondary replica is active/readable? My objective is to have a highly available environment with automatic failover but do we need to sacrifice offloading read operations in order to accomplish this? Adding another server is not an option.

You can fail over to a readable secondary.  The author is either wrong, or just using some confusing terminology.  Reading the exact quote you included, I'd lean towards the former explanation.  But I don't know all the context around that statement.
Here I have an AG - all replicas are configured to be readable when they are in the secondary role, and NODE2 is set up for automatic failover:

Here's the current state of things on the AG dashboard:

I'll go ahead and connect directly to NODE2 (sync secondary) and start some queries running:
SELECT COUNT_BIG(*)
FROM dbo.A;

WAITFOR DELAY '00:00:01';

GO 100

And then do something to cause an automatic failover to NODE2.  In this case, I will kill the SQL Server service on the current primary (NODE1).
Now I'll check the AG dashboard, and can see that NODE2 automatically became the new primary.

Note that during the failover, my SSMS window running queries on NODE2 got this message:

Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Which is normal (clients being disconnected while the database undergoes recovery as part of becoming the primary).  I retried the query and it continued running.  I wouldn't have to do that if I were connecting to the listener via read-only intent.


SQL Server Alerts (notify operator when job failed) I setup database mail, created an operator today and enable notify operator when job failed.

Is that all or do I need to create an alert too?

My boss just needs emails if any job failed.

Also how to test it to see it will work in the future?

Thanks

Regarding the status of job run, that's the correct steps.

However, its recommended you configure the alerts for any database corruption, HW/SW failures or any other cause for an outage. Read here to configure the same.

In order to test, create a TEST job and purposefully make it fail and see if you receive the failure notification email or not.


Hands-off Scripted Install of MySQL 5.7 on Linux I'm a long time user of MySQL, and have historically used Puppet or Ansible to install it onto boxes. I can automatically remove the test accounts/db, set a root password, add some application and monitoring users and databases and then hand the system over for application deployments, knowing everything's setup and ready to go for them.

Since MySQL 5.7, I'm not sure how to approach this. On installation it generates a random root password which it writes into a log file. Obviously, getting that out and working with the restricted account to get it set to the required password is difficult with a script (or config management system).

I've had a go at hacking the mysqld_pre_systemd script (on Centos 7) and have that create the data files, then start up mysql with a --init-file option, which I point to a bit of SQL that sets the password as I want. Once it's running, I then connect to it and run 'SHUTDOWN' to stop it, then let systemd carry on as it was to start it up normally.

This all seems like a crazy amount of work (although quite possible with Ansible etc). As I've now got to get it going on Centos 6, is there a better way?

One thought: Is there a way to specify the root password before installation?

This isn't much of a solution, but it's possible to put the data files in place on the system before installing the RPM packages.

That is, on a dev system, install MySQL and set the root password as you want it. Shutdown Mysql and make a copy of the files/directories in /var/lib/mysql (maybe put them in a tar file or something).

On a new system, before installing the MySQL RPM packages, take a look and see if /var/lib/mysql exists - if not, create it and untar your files into it. When the RPM installs it won't overwrite those files, and so will start up with your previously set root password.

This seems like quite a cheesy option, and probably won't work when versions of the RPMs change (even dot release changes might break because they'll need an upgrade process on the archived data files). However, it's a possible way forward.

Another (less than ideal) way forward is something like this:

Check to see if /var/lib/mysqld exists. Remember this.
Install Mysql RPMs (the post-installs automatically create /var/lib/mysql if it doesn't exist - you can't really stop this from happening)
If (1) was "doesn't exist":

Ensure Mysql is stopped (the RPMs don't start it, but just be sure)
Delete /var/lib/mysql/*
run /usr/sbin/mysqld --initialize-insecure --datadir="/var/lib/mysql" --user=mysql (which is what the init.d script does, if /var/lib/mysql is empty, except we're using 'insecure' which means no root password is generated)
Start mysql
Use mysql client to log on with empty password and set the root password to whatever you want.
I'm really not a fan of using Ansible or whatever to delete database data files. Even though this method has suitable checks to make sure it doesn't delete a live database, it still seems like you're creating a dangerous situation here. However, it's another possible approach.

I guess the last approach is to petition Oracle to allow setting a root password during installation (maybe via an environment variable that the RPM post-install picks up, or a magic file somewhere that contains the password that the post-install looks for. That would make life so much easier ;-)


How to setup Mysql master-slave replication with the slave meant for testing where divergence is OK Problem:
I have a staging DB server on which functionality is tested before pushing it to the production environment. Now, we want to start doing VA/PT (Vulnerability Analysis and Penetration Testing) on our application, but since that can be destructive on the staging DB server, we want to make a separate Testing environment with a VAPT web+DB server.
Requirements:

The data from the staging DB server must be replicated onto the VAPT DB server automatically so that specific new use cases, features, etc can be tested for vulnerabilities.

Due to VAPT activities (testing data, SQL Injection, possibly DROP TABLE exploits, etc) the VAPT DB server itself will also have its own data changes, i.e. divergence from Staging DB (Master)

So, if I use simple Master-Slave replication as below I am assured of #1:
Staging DB (Master) -> VAPT DB (Slave)
But if I do #2, the slave will eventually diverge, which is fine for the testing environment, but, will it interrupt or mess with the Master-Slave replication as per #1?
An obvious example where divergence will cause errors is a VA/PT activity that causes DROP TABLE users so that the Staging DB (Master) users table trying to INSERT/UPDATE data will cause replication errors. Some UPDATEs/DELETEs might cause errors too.
In particular,
If I use ROW-based replication divergence will happen quickly causing frequent errors.
If I use STATEMENT-based replication, since ids will not match, it is possible that some data will break because ids are essential to link data in related tables even though we do not use foreign keys.
Alternatively, instead of replication, I could manually dump the Staging DB into the VAPT DB daily, which would be cumbersome to automate.
OR,
I could make copy DBs and setup various partial copy operations, but that would complicate matters too much, given that I am not a developer and that my developers often make and revert changes of various sizes randomly.
EDIT: The data directory varies between 20-25 GB on Staging
Surely someone has come across this problem in their work so far and there might be a set of best practices for this situation i.e. maintaining a match between staging and testing environments in real-time while allowing testing freedom to play with the data.
I tried googling for a while but the right phrasing for google escapes me. All I get is howtos for master slave replication, handling unwanted drift/divergence and so on. Nothing much about desired/accepted drift and divergence or partial replication.
Thanks in advance.

Given the situation that you’ve described, any form of replication will fail spectacularly within minutes of a rigorous pen test. Having a completely isolated database instance for this purpose alone is the most logical solution.
Here are a couple of reasons why:

as you said, someone issues a DROP TABLE and, because there’s no RI in your database to prevent this and because it sounds like the application is using a user account with full privileges, the command is carried out. Replication won’t “fix this” for you.
Someone creates a new record on the replicated instance. Are all primary keys GUIDs? If so, you might be lucky enough to never run into a primary key collision but if you’re using incremental integers, there will be overlap (even if the replica is configured to use a higher range of numbers)
Someone deletes a record
Someone adds a column, then that same column is later added to the main server
Someone truncates a table
And on and on and on …

Automated restores of your production database should already be something that the company is doing as part of its backup verification process, so I don’t see how this would be cumbersome or difficult to automate. Every operating system under the sun can handle a little script that takes a recent backup and restores it to a database. Unless your system is already many hundreds of gigabytes in size, this would be a very simple and cost effective way forward.
As an added bonus, this script would make refreshing the development database(s) an absolute breeze


MySQL Rows to Columns to HBase (using Sqoop) I have a table that looks like this in MySQL:

id | date | num | avg

I have found that I can use this for rows to columns and it works so far:

SELECT
    CONCAT(id, '-', date),
     MAX(IF(`num` = 0, avg, NULL)) num0
FROM table
GROUP BY
    id,
    date;

The problem is I have 4000 'num' values (values are integers from 0 to 4000) and I do not want to write 4000 MAX statements manually. Is there anyway I can put a loop in there somehow? Ideally, I would like to have 4030 columns...

Edit:

I have edited my post to give some more context to the problem I am facing. I am sorry as I should have probably done this earlier.

Context of problem: I have to migrate data from MySQL to HBase. I want to import the values so that each day (which would be represented by a row key along with the id) have many columns (num) which have a value (avg). The column family is avg. I was originally planning on selecting Sqoop's "Select statement" but I understand I may be pushing the boundaries regarding MySQL's columns and I am not sure what the max is. If anyone knows a better way of doing this and can share with me, that would be great.

If you are interested in returning all the values as columns, you need to be try something very adventurous. First look at your query
SELECT
    CONCAT(id, '-', date),
    MAX(IF(`num` = 0, avg, NULL)) num0
FROM table
GROUP BY
    id,
    date;

If would be a big mess to get MySQL to execute it as a query by giving MySQL the query with each column formulated.
Perhaps you can get MySQL to concatenate the column values using GROUP_CONCAT. That function was designed for aggregation (or aggravation if you are the actual developer). You can take all the num values can display it as a column-separated listed of numbers like this:
SELECT
    CONCAT(id, '-', date),
    GROUP_CONCAT(IF(`num` = 0, avg, 0))) numlist
FROM table
GROUP BY
    id,
    date;

You can also change the list to be delimited by pipes instead of commas like this:
SELECT
    CONCAT(id, '-', date),
    GROUP_CONCAT(IF(`num` = 0, avg, 0)) SEPARATOR '|') numlist
FROM table
GROUP BY
    id,
    date;

The default maximum length of a GROUP_CONCAT is 1024.
You need to change that max length in the session using this:
SET group_concat_max_len = 10240;

before you issue your query.
Give it a Try !!!


Getting "ORA-00942: table or view does not exist" while table does exist I'm fairly new to Oracle database. I have installed Oracle Database 11g R2 on Oracle Linux 6. I have successfully created a new database with dbca and connected to the database using:

$ sqlplus "/ as sysdba"

I successfully created a table and inserted some data and performed some selects:

SQL> CREATE TABLE Instructors (
         tid    NUMBER(7) PRIMARY KEY,
         fname  VARCHAR2(32),
         lname  VARCHAR2(32),
         tel    NUMBER(16),
         adrs   VARCHAR2(128) );

Table created.

SQL> INSERT INTO Instructors (tid, fname, lname, tel, adrs)
     VALUES (8431001, 'John', 'Smith', 654321, 'London');

1 row created.

SQL> SELECT count(*) FROM Instructors;

  COUNT(*)
----------
        1

Then I created a new user with the CONNECT privilege:

SQL> CREATE USER teacher1 IDENTIFIED BY pass1;

User created.

SQL> GRANT CONNECT TO teacher1;

Grant succeeded.

Then I created a new role with appropriate object privileges:

SQL> CREATE ROLE instructor;

Role created.

SQL> GRANT SELECT, UPDATE ON Instructors TO instructor;

Grant succeeded.

And granted the role to the user:

SQL> GRANT instructor TO teacher1;

Grant succeeded.

Next I exited sqlplus with exit; and connected as the new user to test it. I logged in successfully to the database with:

$ sqlplus teacher1

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 25 03:20:50 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password: *****

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

But when I try to select from the table it says:

SQL> SELECT * FROM Instructors;
SELECT * FROM Instructors
              *
ERROR at line 1:
ORA-00942: table or view does not exist

What I'm missing here ?!

You created the table in the SYS schema (which you should never, ever do. Really, never).

When you log in as teacher1 any statement looks for objects in that schema. But there is no TEACHER1.INSTRUCTORS table, because the real name is SYS.INSTRUCTORS (did I mention what a bad idea it is to create objects in the SYS schema?).

You need to run select * from sys.instructors to get access to that table. If you don't want to prefix the table name with the schema, create a synonym in the teacher1 schema:

create synonym teacher1.instructors for sys.instructors;

Then teacher1 can access the table from the SYS schema without fully qualifying it.

Again: stop using the SYS or SYSTEM account for anything that is not DBA stuff. Use a regular account for that.


Understand why the query plan was changed We had some performance issues with our ERP system. Somewhere around 06:00 the problem started. Long story short; one of my colleagues executed a DBCC FREEPROCCACHE (I know that this is not a preferred action, but that is not the point here). After that, he restarted the whole ERP application, not the database engine. That didn't seem to help. I looked into the Query Store and saw that the query plan was changed somewhere around 06:05. I tried forcing the older plan and the performance issues were gone.

I compared the 2 plan XMLs and I saw that both were based on the same statistics (updated date was exactly the same). The number of changes on the indexes was different, but that did not trigger an update statistics, because else the new query plan would have had a new date for the used statistics. I checked all the statistics of the involved tables, but they were not updated since the last maintenance job. The last maintenance job ran this weekend, which was also the update date of the statistics used in both query plans.

The query is a parameterized query, so why the query plan changed suddenly is a little unclear to me. I would think just the opposite, that a query suddenly runs slow, because the same plan is used for different parameters. This did not seem to be the case here, because I forced the old query plan.

The query comes from some bespoke work that retrieves orders. Now, I suspect that someone issued the parameterized query which would have returned a very large dataset and that slowed down the ERP system. Then, my colleague ran a DBCC FREEPROCCACHE and that triggered the re-compile of the query plan, based on the new statistics from this weekend. The current query just kept running and that is were he decided to restart the ERP application. Now with the new query plan all new queries slowed down until I forced the old query plan.

Could this be the explanation? I am a little skeptical, because SQL Server would trigger a re-compile when statistics have changed, so the plan should already have been re-compiled after the index maintenance job.

I think I am missing something here, but I am not exactly sure what. It is SQL Server 2016 Standard and is a dedicated server for the ERP system.

Edit 15-4-2020 12:17: Now that I write this... I think the issue is just that after the DBCC FREEPROCCACHE the parameters of the first execution of the parameterized query in question used different parameters, which caused SQL Server to recompile the (bad) plan. Or is there still something I am missing?

Statistics are one input to the query optimizer but not the only one.  As mentioned in the post, the optimizer also sniffs parameter values during compilation and uses those to generate estimates of the number of rows that will come out of various operations such as filters, joins, and group by using the statistics that exist at the time of compilation.  Furthermore, the storage engine provides row counts for each compilation and this can also change without stats changing (though if you change enough rows, the optimizer eventually forces stats to be recomputed).  Any of these things changing could be the cause of the plan changing from one compilation to the next.  The query store actually also tracks other factors that it internally uses to represent to you whether the query is the same or different semantically through a table called sys.context_settings.  So, ansi null on/off would be one such semantic change that could also cause differences in results.  The query store abstracts these from you, but if you were to, for example, create a loop in a stored procedure that changed ansi nulls on/off and then run the same statement over and over in the loop, you'd actually get that to compile over and over (each time the settings are changed from the last time the statement in that part of the sproc is compiled) and this would also give you a chance to have new parameters sniffed on each execution.  These would technically show up as different queries in the query store but to the SQL engine these are holding the same statement slot in the cached query plan for the stored procedure.

SQL 2017+ contains a feature called Automatic Tuning which can auto-force the old plan for you for large regressions.  Azure SQL DB also has this same feature.  In the future, we hope to make parameter sniffing less of a problem for customers, so I hope this gives you insight in the meantime to work around the issue for your scenario.


Is it good practice to rebuild index on FileTable? We are using Ola Hallengren scripts, both to backup our databases and for index maintenance.
We have a FileTable in our database and from time to time, REBUILDING its PK index takes close to three (3) hours, the rest of the time it is very fast.
Questions :
is it a good practice to reindex a FileTable ?
which is abnormal, taking only a few seconds or taking a few hours ?
I have done a few searches about reindexing a filetable but have not really found anything useful.

(converting my comment to answer)

FileTable uses Filestream technology. From Paul Randal's blog - Defrag the NTFS volume if needed before setting up FILESTREAM, and periodically to maintain good scan performance.

Instead of rebuilding, you should defrag the ntfs volume as suggested.

Also, highly suggest to read - Best Practices on FILESTREAM implementations - by Pedro Lopes (Senior PM at Microsoft Database Systems)


What is the best way to automate a migration of multiple SQL Databases to a different hard disk? We have a SQL Server with about 50 databases. Of those databases, about 25 of them need to have their .mdf and .ldf files transferred from drive D: to drive E:. It is critical that the database names all remain the same.

What is the best, safest way to do this in an automated fashion?

My initial idea was to use a sort of pseudo ForEach Loop, supply a list of database names, and have it loop through queries to backup and restore or detach and re-attach the databases, but I have not been able to properly insert the database name variables into SQL queries within a loop.

Something as simple as the below can construct your script

SELECT
'ALTER DATABASE ' + QUOTENAME(d.name) + ' MODIFY FILE (name=N'''+[f].[name]+ ''', filename = N''' + REPLACE(f.[physical_name],'G:','E:') + ''')'

FROM sys.databases d
    INNER JOIN sys.[master_files] f ON d.[database_id] = f.[database_id]
WHERE d.[database_id] > 4

You can then take the output and save it, or you could potentially wrap it further to turn it into dynamic SQL to actually run it

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'';

SELECT @SQL +=
'ALTER DATABASE ' + QUOTENAME(d.name) + ' MODIFY FILE (name=N'''+[f].[name]+ ''', filename = N''' + REPLACE(f.[physical_name],'G:','E:') + ''')'

FROM sys.databases d
    INNER JOIN sys.[master_files] f ON d.[database_id] = f.[database_id]
WHERE d.[database_id] > 4

PRINT @SQL
--EXEC (@SQL)

Note the use of REPLACE to deal with the change of file location

For additional parameters and commands the above script can be edited easily to include things such as setting the database to single_user or offline.

Following this the SQL Server should be brought offline, the files moved in the file system, and the server brought back online.


Finally as I did not see any hope I inserted a column "prodname" and with this sql I got the result. But any help will be welcome this add of prodname makes my TB_A table a little bulky.

Here is the SQL

SELECT sd.prodname, sd.date,
SUM(IF((sd.status=0 AND (sd.prodid=pb.id OR sd.prod.id=pm.id OR sd.prodid =pg.id)), sd.qty,0)) AS tot,
SUM(IF((sd.status >0 AND (sd.prodid=pb.id OR sd.prodid=pm.id OR sd.prodid=pg.id)), sd.qty, 0)) AS rq
FROM TB_A AS sd
LEFT JOIN TB_B AS pb ON pb.id=sd.prodid
LEFT JOIN TB_C AS pm ON pm.id=sd.prodid
LEFT JOIN TB_D AS pg ON pg.id=sd.prodid
WHERE sd.stat>1
GROUP BY pm.id,pm.id,pg.id,sd.prodname
ORDER BY sd.date DESC

PS:In my display of my program I don't display rq I display tot-rq within a program. Any suggestion for getting that in this query is welcome.