unenforced constraints/possible duplication in my database design? I am trying to store information about a school and the class timetable in a database. The outline is as follows:

'A subject has ~20 lessons, each of which needs to have it's description stored in the database. The topic of each lesson is also kept.

Each topic is also taught by a different teacher(s) - some topics are taught by multiple teachers. Each teacher only teaches a single subject and topic.'

So far I have this:

topics:
(id (PK), topic_name)

subjects:
(id (PK), subject_name)

lessons:
(id (PK), lesson_desc, subject_id (FK), topic_id (FK))

teachers:
(id (PK), teacher_name, subject_id (FK), topic_id (FK))

teachers_and_lessons:
(teacher_id (PK), lessons_id (PK))

However, my concern is that it does not enforce certain constraints, and information is perhaps being unnecessarily duplicated.

For example, there is no enforced constraint against me adding a (teacher, lesson) pair to teachers_and_lessons where the teacher's subject_id is 4 but the lesson's subject_id is 5.

Assuming the data is valid, you can also infer the the subject_id and topic_id of the lesson or the teacher by using teachers_and_lessons and either the lessons or subjects table. For example, a teacher with record (1, roger, 1, 2). If the record (1,3) exists in teachers_and_lessons, then the lesson with id = 3 must have subject_id = 1 and topic_id = 2. Note also: a lesson or teacher should be able to exist without having been assigned anything.

Does anyone know a  better way of structuring my database? Thanks

Your model includes referential cycles.  This comes up quite a bit when you're modeling and you haven't worked through the differences between what relationships exist and what relationships are important to your system.

Without knowing more about your business rules and their justification, I would suggest that you consider dropping teachers.topic_id and possibly also teachers.subject_id.

Your lessons table relates subjects and topics.  Since you have a many-to-many relationship between teachers and lessons, you can use lessons (via teachers_and_lessons) to tell you which teachers can teach which topics and which subjects.

I doubt that neither teachers.topic_id nor teachers.subject_id are correct anyway, since this would imply that any given teacher can only teach one topic and one subject.  I don't know about your school, but it sounds much too specialized to me!

Edit: Recognizing Teaching Qualifications

Based on a comment by OP (below) the question is how can a teacher be recognized as being qualified to teach a topic without there being an actual lesson assignment made (yet)?

Issue 1: The relationship of topics to subjects:

It isn't clear from the original question whether topics belong to one subject or possibly to multiple subjects.  If topics (e.g. fractions) belong to only one subject (e.g. math) then there should be a foreign key like so: topics.subject_id.  If this were so, then lessons.subject_id would be redundant and could also be dropped.  This would simplify the model and make things much clearer while reducing the risk of inconsistent foreign keys.

Issue 2: What teachers can do vs. what they are doing:

The teachers_and_lessons table shows which lessons teachers are actually teaching.  Before this assignment is made, OP would like to be able to record which topics a teacher is qualified to teach.  This could be done with a second M:N intersection, like so:

teacher_qualifications:
(teacher_id (PK/FK), topic_id (PK/FK))

This design assumes the simplification suggested under Issue 1, above.  One potential drawback of this simplistic solution is that there is no constraint against assigning a teacher to teach a lesson for which they are unqualified.  This constraint, if it is important, could be accommodated by replacing the teacher_qualifications and the teachers_and_lessons tables with new tables as follows:

teacher_qualifications:
(qualification_id (PK), teacher_id (FK,UN1), topic_id (FK,UN1))

lesson_instructor:
(qualification_id (PK,FK), lessons_id (PK,FK))

Again, these tables assume the simplification from Issue 1.  Even without that simplification, these tables could be amended to use an analogous approach.


I have following two MySQL queries and i want to execute it inside one procedure Following query returns 10 rows of recipe id's i wants to pass first query's output which is of 10 rows to the second query and display the output of both the queries inside a procedure.

Query 1:

SELECT
    p.ID as recipe_ID,
    p.post_title as recipe_title,
    p.post_date as modified_date,
    p.post_name as recipe_url,
    CASE WHEN s.total_count THEN s.total_count ELSE 0 END as total_like_count,
    CASE WHEN c.bookmark=1 THEN 1 ELSE 0 END as bookmark_status,
    CASE WHEN c.like_status=1 THEN 1 ELSE 0 END as like_status,
    CASE WHEN pm.meta_value=1 THEN 1 ELSE 0 END as unlock_status

FROM
    posts p
LEFT JOIN social_val s on(p.ID=s.post_id)
LEFT JOIN social_status_connection as c on(p.ID=c.post_id AND c.user_id=1)
LEFT JOIN postmeta pm on(p.ID=pm.post_id AND pm.meta_value=1 AND pm.meta_key='recipe_lock'),
term_relationships as t, term_taxonomy tt
WHERE
  p.post_type = 'recipe'
    AND p.post_status = 'publish'
    and p.ID = t.object_id
    and tt.term_id = 1279
    and t.term_taxonomy_id=tt.term_taxonomy_id

ORDER BY modified_date DESC
LIMIT 0 , 10

Query 2
Below query is self join which generates image of above particular recipe id.

SELECT
    p.guid as recipeImageURL
FROM
    posts p,
    posts m
where
    p.post_type = 'attachment'
        AND p.post_parent = m.ID
        AND m.ID = ID ORDER BY p.post_modified DESC
LIMIT 1;

If you need to loop over the output of one query to another you can use cursors.

For eg, in this stored routine to find in all tables databases we have looped over:

DECLARE table_cur CURSOR FOR SELECT ...
OPEN table_cur;
table_loop:LOOP
...code...
END LOOP;
CLOSE table_cur;

So in your case declare cursor for first query and loop over each recipeid.


Merge two rows letting the second one override the first in PostgreSQL Say that you want to build an append only table in Postgres, where each modification is actually just a new row added to the table. Say that you have the below simple, and disregard all questions about performance, table of users.

create table user_revisions (id uuid, name text, password text, version bigserial);
insert into user_revisions (id, name, password)
values ('743ccdf0-e9d8-4268-b6d7-0645eb70feb9', 'Bengan', 'nthusaeo');
insert into user_revisions (id, name, password)
values ('743ccdf0-e9d8-4268-b6d7-0645eb70feb9', 'Bengan', 'åäö');

Getting the latest version of a user would look something like:

select * from user_revisions e1
where e1.id='743ccdf0-e9d8-4268-b6d7-0645eb70feb9'
order by version desc limit 1;

But then you come to the part where you want to start modifying the users. Each request to the database always has the uuid of the user being modified and some subset of all the fields that are otherwise present in the user_revisions table.
You want to make an insert with the last revision of the user as a set of default values?  That is, do the equivalent of the Clojure code (merge a b) or Python {**a, **b}, where the two associative data structures a and b are merged and if there are any keys in a that also exist in b the values held in b will override those of a.

Below are some failed attempts at writing a select that produces the output that we in turn wants to insert as the latest revision.

with temp (password) as (values ('snthsnth'))
select * from user_revisions e1 natural left join temp
where e1.id='743ccdf0-e9d8-4268-b6d7-0645eb70feb9';

with temp (password) as (values ('snthsnth'))
select * from user_revisions e1 natural inner join temp
where e1.id='743ccdf0-e9d8-4268-b6d7-0645eb70feb9';

with temp (password) as (values ('snthsnth'))
select * from user_revisions e1 natural right join temp
where e1.id='743ccdf0-e9d8-4268-b6d7-0645eb70feb9';

The desired output would be:

 password  |                  id                  |  name
-----------+--------------------------------------+--------
 snthsnth  | 743ccdf0-e9d8-4268-b6d7-0645eb70feb9 | Bengan

This output would then be inserted into the table as the latest version of the user with the id 743ccdf0-e9d8-4268-b6d7-0645eb70feb9.

It depends if you need something generic or if it can work with a "small" number of columns.

In your specific example, this query:

WITH new_data (id, name, password) AS (values ('743ccdf0-e9d8-4268-b6d7-0645eb70feb9'::uuid, NULL, 'snthsnth'))
SELECT
id,
COALESCE(new_data.name, current_data.name) AS name,
COALESCE(new_data.password, current_data.password) AS password,
current_data.version
FROM user_revisions current_data
INNER JOIN new_data USING (id)
WHERE id='743ccdf0-e9d8-4268-b6d7-0645eb70feb9'
ORDER BY current_data.version DESC LIMIT 1;

would produce the expected:

id                                      name    password    version
743ccdf0-e9d8-4268-b6d7-0645eb70feb9    Bengan  snthsnth    2

You can test things in this SQLFiddle: http://sqlfiddle.com/#!17/c4700/5/0

I am quite confident it could be made generic if needed, but it may be enough already for your needs?

However it will not work correctly if you need to store NULL values, and if you need to handle transition such as "some data value => NULL"

In such cases there would be a specific need to realy test the existence of each field and just take its value if present, including if it is NULL

In passing I would also say like @Lennart that it is better to separate archive data from live data for many reasons, one would be performance, to compute the "next" version you will only have to read one row (the live one) and then doing something as above, instead of having to do things like order by version desc limit 1 each time, which will be more costly, even with an index on version.


New Table vs Adding Columns To Existing Table I have a table, products with the following columns: id, name
There is another table, countries with these columns: id, name, code
The products have different prices across different countries (same currency is used).

I'm torn between creating a new table to store this, which would have the following columns: product, country, price
and adding new colums to the products table; price_uk, price_kenya, price_sweden, etc, for the different countries.
Which would be the better option, and why?

Instead of arguing normalization rules, I will use predicates and constraints.

[P1] Product number (PRODUCT_ID) named (PRODUCT_NAME) exists.

(c1.1) Product is identified by product number.

(c1.2) For each product that product has exactly one product name; for each product name exactly one product has that product name.

product {PRODUCT_ID, PRODUCT_NAME}  -- P1
    KEY {PRODUCT_ID}                -- c1.1
    KEY {PRODUCT_NAME}              -- c1.2

[P2] Country named (COUNTRY_NAME) with assigned country code (COUNTRY_CODE) and number (COUNTRY_ID) exists.

(c2.1) Country is identified by country number.

(c2.2) For each country, that country is assigned exactly one country name; for each country name, that country name is assigned to exactly one country.

(c2.3) For each country, that country is assigned exactly one country code; for each country code, that country name is assigned to exactly one country.

country {COUNTRY_ID, COUNTRY_NAME, COUNTRY_CODE}  -- P2
    KEY {COUNTRY_ID}                              -- c2.1
    KEY {COUNTRY_NAME}                            -- c2.2
    KEY {COUNTRY_CODE}                            -- c2.3

[P3] Product (PRODUCT_ID) in country (COUNTRY_ID) is assigned price of (AMOUNT) (CURRENCY).

(c3.1) For each country and product, that combination of that country and that product occurs at most once.

(c3.2) For each combination of country and product, that product in that country has exactly one price.

(c3.3) For each price, it is possible that more than one product in a country has that price.

(c3.4) If a product is assigned a price for a country, then that product must exist.

(c3.5) If a product is assigned a price for a country, then that country must exist.

country_price {COUNTRY_ID, PRODUCT_ID, AMOUNT, CURRENCY}  -- P3
          KEY {COUNTRY_ID, PRODUCT_ID}                    -- c3.1

          FK1 {PRODUCT_ID} REFERENCES product {PRODUCT_ID} -- c3.4
          FK2 {COUNTRY_ID} REFERENCES country {COUNTRY_ID} -- c3.5

Note:

[Px]   = predicate  x
(cx.y) = constraint x.y

KEY = PK or AK

PK  = PRIMARY KEY
AKn = ALTERNATE KEY (UNIQUE)
FKn = FOREIGN KEY

So this is basics, as per your first suggestion.

Note that adding pricing for a new country does not involve schema changes, but adding data. What happens to the application code if you were to add a column to the product table instead?

Consider the following questions:

For which countries do we have all product prices defined?
For which countries we do not have any product pricing defined?
Which countries do not have pricing for all products defined -- but do have at least one?
Which product prices are not defined in country X?

These are straightforward queries. An analyst may prepare these queries and the code will not change as you keep adding new countries and products. Now try to formulate these queries for a model where you add columns to the product table. What happens to the query code if you were to add a column to the product table?


WCS8 Solr Preprocess failed : Error SQLCODE=-601, SQLSTATE=42710, SQLERRMC=WCSSTGAP.XI_CATENTRY_PRICE_0 Our Solr Pro-process is failing with below error:

  [2018/08/28 14:31:36:104 CDT] 00000001 W
  com.ibm.commerce.foundation.dataimport.preprocess.AbstractDataPreProcessor:createDBTable(Connection,
  String, String) create XI_CATENTRY_PRICE_0 with error: DB2 SQL Error:
  SQLCODE=-601, SQLSTATE=42710,
  SQLERRMC=WCSSTGAP.XI_CATENTRY_PRICE_0;TABLE, DRIVER=4.19.49

I suspect that DROP table XI_CATENTRY_PRICE_0 is failing and ultimately causing the creation of the existing table.

How could I resolve this?

The -601 happens because table WCSSTGAP.XI_CATENTRY_PRICE_0 already exists , so it cannot be created again while it exists.

Either drop that table manually and retry, or check back in the log file(s) to find if a previous DROP TABLE WCSSTGAP.XI_CATENTRY_PRICE_0  did fail (and then solve that problem first). You will need to find root cause.


Application queries are not getting generated in trace file in oracle I am getting queries in trace file which are fired from Oracle SQL Developer IDE but I want to trace queries on Oracle database, which got fired from my java application.

I'm using ALTER SESSION SET SQL_TRACE = TRUE; command and using tkprof utility.

There are a couple of ways of starting a trace in another session.

You can either use a login trigger that sets tracing in the session when it first logs on:

CREATE OR REPLACE TRIGGER TRACE_MY_JAVA
AFTER LOGON ON DATABASE
BEGIN
    IF USER = 'MYJAVAUSER' -- edit this condition to pick out your Java sessions
  THEN
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
  END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

Or, if the session has already started, you can use dbms_system.set_sql_trace_in_session. Get the other session from v$session:

SELECT sid, serial#
FROM v$session
WHERE -- whatever you need to identify your Java application;

Then execute the proc:

execute dbms_system.set_sql_trace_in_session (sid, serial#, TRUE);

(Add the sid and serial# from the above query)


Lower() vs ilike on postgresql What I want to do is to check if value of column equals some string, but case insensitive. Is there a performance difference when using ilike without wildcards (percents) and lower() ?

I was planning to create index on lower(column_name) and use lower(column_name) = value (value is lower case already) but it turns out that ORM I'm using doesn't allow me to use lower() in a pretty way (but has convenient method for ilike). Will column_name ilike 'value' use lower(column_name) index?

Measure it and see, using explain analyze on test data.

I would expect a significant difference between an expression index on lower(col) with lower(col) = lower('constant') vs use of col ilike 'constant', with the expression index on lower(col) the faster.

That's because Pg can do a bitmap index scan with the expression index, but for the ilike it'll have to do a seqscan. AFAIK a text_pattern_ops index (useful for LIKE) won't do you any good for ILIKE.

The other question is whether the difference is worth caring about, and that depends on your data and workload. Sometimes it's smarter to just choose the slower but easier option and spend yourtime/effort elsewhere.

  Will column_name ilike 'value' use lower(column_name) index?

No. Nor will it use an index on lower(column_name) text_pattern_ops. AFAIK there is no way to index a case-insensitive pattern match (ILIKE or ~~*) on a case-sensitive text/varchar in PostgreSQL at this time.

I don't think the citext type changes this, though it does make things more convenient.


Is it possible to automate the creation of multiple FEDERATED tables in MySQL? From the MySQL documentation on creating tables for to access data on a remote MySQL server using the FEDERATED storage engine:

  The basic structure of this table should match that of the remote
  table, except that the ENGINE table option should be FEDERATED and the
  CONNECTION table option is a connection string that indicates to the
  FEDERATED engine how to connect to the remote server.

I have a lot of existing tables that I want to federate against another server.  Is there a way to make this process quicker? Or I must go, table-by-table, copying and editing the definitions and creating the federated version of each table locally?

I found the command to extract the existing table definition from the remote server:

mysql> SHOW CREATE TABLE wp_options;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wp_options | CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(64) NOT NULL DEFAULT '',
  `option_value` longtext NOT NULL,
  `autoload` varchar(20) NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB AUTO_INCREMENT=999648 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

So it seems like all I have to do is modify the ENGINE and add a CONNECTION definition... still, having to create federated tables manually for each of the existing tables is a time-consuming task.

Does MySQL have a way to do this automatically for a group of tables, or all tables?

There's not an internal way to do this, but it's easy enough to automate with a script, like the following:

#!/usr/bin/env perl
use strict;
use warnings;
use DBI;

my $sleep_between = 1; # artificial delay; set to zero to remove

my $origin = 'origin_server_hostname';
my $oport = 3306; # origin server port
my $ou = 'origin_server_username';
my $op = 'origin_server_password';

my $fu = $ou; # or change, if federation needs a different user
my $fp = $op; # or change, if federation needs a different user

my $target = 'target_server_hostname';
my $tport = 3306; # target server port
my $tu = 'target_server_username';
my $tp = 'target_server_password';

warn "connecting to origin server";
my $odbh = DBI->connect("DBI:mysql:information_schema;host=$origin;port=$oport","$ou","$op",{RaiseError => 1, mysql_auto_reconnect => 0});
warn "connecting to target server";
my $tdbh = DBI->connect("DBI:mysql:information_schema;host=$target;port=$tport","$tu","$tp",{RaiseError => 1, mysql_auto_reconnect => 0});

warn "fetching origin tables...";
# change the query to select only the tables you want to federate; existing tables won't be overwritten
my $otables = $odbh->selectall_arrayref(qq{
        SELECT table_name, table_schema
          FROM information_schema.tables
         WHERE table_type = 'BASE TABLE'
           AND table_schema NOT IN ('mysql','information_schema','performance_schema')
},{Slice => {}}
);

my $prev_db = '';
foreach (@$otables)
{
        my ($os,$ot) = ($_->{table_schema}, $_->{table_name});
        my ($ts,$tt) = ($os,$ot);

        my $sct = $odbh->selectrow_arrayref(qq{SHOW CREATE TABLE `$os`.`$ot`})->[1];
        $sct =~ s/^CREATE\sTABLE\s`$ot`/CREATE TABLE IF NOT EXISTS `$ts`.`$tt`/ || die "regex replacement failed: $sct";
        $sct =~ s/\)\sENGINE=\S+\s/) ENGINE=FEDERATED / || die "regex replacement failed: $sct";
        my $fcs = "CONNECTION='mysql://$fu:$fp\@$origin:$oport/$os/$ot'";
        $sct .= ' ' . $fcs;
        warn "$sct\n\n";
        if($prev_db ne $ts)
        {
                my $dbdef = $odbh->selectrow_arrayref(qq{SHOW CREATE DATABASE IF NOT EXISTS `$os`})->[1];
                print STDERR "$dbdef ... ";
                print STDERR ($tdbh->do($dbdef) ? 'ok' : 'error') . "\n\n";
                $prev_db = $ts;
                sleep $sleep_between;
        }
        print STDERR "creating table ${ts}.${tt}... ";
        print STDERR ($tdbh->do($sct) ? 'ok' : 'error' ) . "\n\n";
        sleep $sleep_between;
}

$odbh->disconnect;
$tdbh->disconnect;

warn "done";
# # #

Save the script to a file, e.g. federation-automate.pl.

Modify the variables at the top to specify the origin (server with the base tables) and target (server where you want to create federated tables) hostnames, ports, usernames, and passwords.  Modify the information_schema query to select fewer tables, if you don't want to federate the whole server.

All modern systems have Perl, but you need DBD::mysql for Perl, if you don't have it.  On Ubuntu, that's...

$ sudo apt-get install libdbd-mysql-perl

Then run the script.

$ perl federation-automate.pl

It loads the table definitions from the origin, and rewrites the SHOW CREATE TABLE statements to build federated tables.  It will also attempt to create the database containing each table, if the database doesn't already exist on the target server.

Note that federated tables don't actually support foreign key constraints, ROW_FORMAT=COMPRESSED, and some other common table options... however, table definitions containing these elements are still valid, and will be accepted, by the federated engine, so there's no need for this code to scrub them out.

Minor modifications to the code would allow you to change the database and/or table names on the target server if you didn't want them to be the same as the origin, for some reason.


MySQL : Till when MySQL retains binary logs? I have multiple queries :

Till when does MySQL retain the binary logs ? What is the default setting ? When does it start logging ?

What does the number of the binary log file name suggest for example in mysql-bin.xxxxxx what does xxxxxx signify.

I have oldest file with name mysql-bin.000163 how can check whether this file was the first binary log file ?

mysql-bin.index contains :

/var/log/mysql/mysql-bin.000163
/var/log/mysql/mysql-bin.000164
/var/log/mysql/mysql-bin.000165
/var/log/mysql/mysql-bin.000166
/var/log/mysql/mysql-bin.000167
/var/log/mysql/mysql-bin.000168
/var/log/mysql/mysql-bin.000169
/var/log/mysql/mysql-bin.000170
/var/log/mysql/mysql-bin.000171
/var/log/mysql/mysql-bin.000172
/var/log/mysql/mysql-bin.000173
/var/log/mysql/mysql-bin.000174
/var/log/mysql/mysql-bin.000175
/var/log/mysql/mysql-bin.000176
/var/log/mysql/mysql-bin.000177
/var/log/mysql/mysql-bin.000178
/var/log/mysql/mysql-bin.000179
/var/log/mysql/mysql-bin.000180
/var/log/mysql/mysql-bin.000181
/var/log/mysql/mysql-bin.000182
/var/log/mysql/mysql-bin.000183
/var/log/mysql/mysql-bin.000184
/var/log/mysql/mysql-bin.000185
/var/log/mysql/mysql-bin.000186
/var/log/mysql/mysql-bin.000187
/var/log/mysql/mysql-bin.000188
/var/log/mysql/mysql-bin.000189

The number in the mysql-bin.nnnnnn filename is simply a sequence number.  This gets updated every time a FLUSH LOGS is initiated, either manually or through predetermined means and is sequential (e.g. normally starts at 00001 and goes up).

The logs will be kept forever unless you specify an expiry.  This should be done in your MySQL configuration:

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_expire_logs_days

You can also delete the binary logs manually within MySQL by using the command:

PURGE BINARY LOGS .... [TO FILENAME | BEFORE 'DATE' ]

See details here http://dev.mysql.com/doc/refman/5.6/en/purge-binary-logs.html

It is possible to delete the files manually (outside of MySQL) but NOT recommended.

See http://dev.mysql.com/doc/refman/5.6/en/binary-log.html for details on the binary logging system.


Run a stored procedure only the last 3 days in a month How can I run a stored procedure only the last 3 days of a month using a T-SQL script?

Adding this script will help you to execute the SP only on the last three days of month.

Note: In the scheduler you make a schedule to execute it daily.

DECLARE @FromDate DATE
DECLARE @ToDate DATE
SELECT @FromDate=DATEADD(DD,-2,CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) AS DATE)) --Last Date -2
SELECT @ToDate= CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) AS DATE) --Last Date of current month

IF (GETDATE() BETWEEN @FromDate AND @ToDate)
BEGIN

EXEC SP_YourSPName--------------------------Execute your SP here

END

I took query to get the last date of the month from https://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0))

Same can be done using the query @Chris Rolliston mentioned in his comment.

DECLARE @Today DATE = GETDATE();
DECLARE @EOM DATE = EOMONTH(@Today);
IF (DAY(@EOM) - DAY(@Today) BETWEEN 0 AND 2)
EXEC SP_YourSPName;

Thanks!


I need to store count of ROWS in table to variable in which value of a column called 'procossed' =0, if value of variable >0 will execute next step create PROCEDURE [dbo].[tablepass12](@TableName nvarchar(20))
AS
BEGIN
DECLARE @SQL VARCHAR(8000)
declare @sql1 VARCHAR(1000)
declare @counts numeric(4)
set @counts=0;
SET @sql1='SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT '+@counts+' = count(*) FROM '+@TableName+' where processed=0"';
EXEC MASTER..XP_CMDSHELL @sql1;
IF @ COUNTS>0
SET @SQL='SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM '+@TableName+'" -o d:\processing\'+@TableName+'.csv';
EXEC MASTER..XP_CMDSHELL @SQL;
END;

Error occured is:

  Msg 8115, Level 16, State 6, Procedure tablepass12, Line 9 Arithmetic
  overflow error converting varchar to data type numeric.

Using xp_cmdshell for this kind of tasks is a very bad idea in my opinion. However, assuming that you have a good reason to be doing things this way, this is a possible solution:

CREATE PROCEDURE [dbo].[tablepass12](@TableName nvarchar(20))
AS
BEGIN
    DECLARE @output TABLE (output nvarchar(max));
    DECLARE @SQL VARCHAR(8000);
    declare @sql1 VARCHAR(1000);
    declare @counts numeric(4);
    set @counts=0;
    SET @sql1='SQLCMD -S ICB3110\SQLEXPRESS -d teena -h -1 -E -Q "SET NOCOUNT ON; SELECT count(*) FROM '+@TableName+' where processed=0"';
    INSERT @output
    EXEC MASTER..XP_CMDSHELL @sql1;
    SELECT @counts = CAST([output] AS int) FROM @output WHERE [output] IS NOT NULL;

    IF @COUNTS>0
    BEGIN
        SET @SQL='SQLCMD -S SQLCLP01\SQL2012 -d tempdb -E -Q "SELECT * FROM '+@TableName+'" -o d:\processing\'+@TableName+'.csv';
        EXEC MASTER..XP_CMDSHELL @SQL;
    END
END;


SQL Server Deadlock Graph - Table, Page or Row Lock? Is there any way to decipher if a lock in a deadlock graph is Table, Page or Row level? I have all the information I need from the graph, including the Isolation Level, (2) but I really want to know this, too.

Thanks to anyone who can help!

In the deadlock graph XML you will see something like:

<deadlock-list>
  <deadlock victim="...">
    <process-list>
      <process id="..." ... waitresource="X:..."
...

The X is the interesting bit, possible values you are interested in are:

RID for row id (row level locking)
PAG for page level lock
OBJECT (which may be further qualified TAB indicating a table lock)

There are a few others kinds listed in the documentation also.


MySQL server installation file download for windows I need to install latest MySQL community server 5.6.25 in windows box.

MySQL site is giving complete MySQL installer, but not particular MySQL server installation file.

Where can I get this particular MySQL server installation file.

There is a "portable" ZIP package of version 5.6.27 (not 25) for download:
http://dev.mysql.com/downloads/mysql/5.6.html#downloads

For completeness, to install and run the server using an unprivileged account:

unzip
rename my-default.ini to my.ini
update base and data directory path in my.ini (data directory must exist)
Open a command window in the installation path.
run the server once with ".\bin>mysqld.exe --log_syslog=0 --console --standalone --initialize" to set-up a fresh database (note: --initialize might be --bootstrap in that version)
run the server with "mysqld.exe --log_syslog=0 --console  --standalone" (--console only if you want the output in the terminal window)
enjoy :)

"mysqld" can be installed as a background service (d for daemon), but you cannot do this without admin rights.


What is an accepted method of keeping old data in MySQL? I think this may be more of a programming question, but since it directly relates to MySQL, I wanted to ask it here.

Some background:  I am running an application whereby users can take tests using data from two tables:  questions and answers.  Each response from every test is recorded in a test_data table.  Now, over time, I will have questions that were either put in wrong, or are no longer relavant.  How would I go about keeping that data somewhere in the database so that I maintain referential integrity?

I seem to remember using an application in the past that had an "archive" table with a few columns:

id
type (describes what data is archived in this row)
data (serialized PHP array)
etc...

This could be considered a subjective question, yes.  But what I'm looking for here are some possibilities that I can use to build an archiving system with in MySQL.

Thanks in advance :)

You could duplicate the table structure entirely, but under a different name, ie. questions_archive. Then move all of the now-irrelevant data into that table and only refer to it in your query via UNION with the original table if it is truly necessary. To maintain referential integrity you'll likely want to create a similar archive table for answers.

You should also be able to specify this table's data be stored on a higher-latency, higher-capacity volume [ie: spindle drive vs SSD, NFS share vs local, etc] if you are looking for further optimization for your non-archived data.

edit: Alternatively, partitioning.


Detecting the locked table or row in SQL Server I'm trying to understand/learn how to track down the details of a blocked session.

So I created the following setup:

create table foo (id integer not null primary key, some_data varchar(20));
insert into foo values (1, 'foo');
commit;

Now I connect to the database twice from two different clients.

The first session issues:

begin transaction
update foo set some_data = 'update'
  where id = 1;

I explicitly do not commit there in order to keep the locks.

In the second session I issue the same statement and of course that one waits due to locking. Now I'm trying to use the different queries floating around in order to see that session 2 is waiting for the foo table.

sp_who2 shows the following (I removed some columns to only show the important information):

SPID | Status       | BlkBy | DBName   | Command          | SPID | REQUESTID
-----+--------------+-------+----------+------------------+------+----------
52   | sleeping     |   .   | foodb    | AWAITING COMMAND | 52   | 0
53   | sleeping     |   .   | foodb    | AWAITING COMMAND | 53   | 0
54   | SUSPENDED    | 52    | foodb    | UPDATE           | 54   | 0
56   | RUNNABLE     |   .   | foodb    | SELECT INTO      | 56   | 0

This is expected, session 54 is blocked by the un-committed changes from session 52.

Querying sys.dm_os_waiting_tasks also shows this. The statement:

select session_id, wait_type, resource_address, resource_description
from sys.dm_os_waiting_tasks
where blocking_session_id is not null;

returns:

session_id | wait_type | resource_address   | resource_description
-----------+-----------+--------------------+---------------------------------------------------------------------------------
        54 | LCK_M_X   | 0x000000002a35cd40 | keylock hobtid=72057594046054400 dbid=6 id=lock4ed1dd780 mode=X associatedObjectId=72057594046054400

Again this is expected.

My problem is, that I can't figure out how to find the actual object name that session 54 is waiting for.

I have found several queries that are joining sys.dm_tran_locks and sys.dm_os_waiting_tasks like this:

SELECT ....
FROM sys.dm_tran_locks AS l
  JOIN sys.dm_os_waiting_tasks AS wt ON wt.resource_address = l.lock_owner_address

But in my above test scenario this join does not return anything. So either that join is wrong or dm_tran_locks doesn't actually contain the information I'm looking for.

So what I am looking for is a query that returns something like:
"session 54 is waiting for a lock in table foo".

Some background info:

The real life problem I'm trying to solve is a bit more complicated, but boils down to the question "on which table is session 54 waiting for". The problem in question involves a largish stored procedure that updates several tables and a select from a view that accesses some of those tables. The select statement is blocked even though we have snapshot isolation and read committed snapshot enabled. Figuring out why the select is blocked (which I thought would not be possible if snapshot isolation is enabled) will be the next step.

As a first step I'd like to find out on what that session is waiting.

I think this does what you need.

USE 'yourDB'
GO
SELECT
    OBJECT_NAME(p.[object_id]) BlockedObject
FROM    sys.dm_exec_connections AS blocking
    INNER JOIN sys.dm_exec_requests blocked
        ON blocking.session_id = blocked.blocking_session_id
    INNER JOIN sys.dm_os_waiting_tasks waitstats
        ON waitstats.session_id = blocked.session_id
    INNER JOIN sys.partitions p ON SUBSTRING(resource_description,
        PATINDEX('%associatedObjectId%', resource_description) + 19,
        LEN(resource_description)) = p.partition_id


SQL Server - Order by case statement very slow I have the following query in a SP which executes fine:

SELECT * FROM MyTable u
ORDER BY
u.[Id] DESC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

However, when I make the ORDER BY configured from a SP parameter the query goes from executing in ms to several seconds:

SELECT * FROM MyTable u
ORDER BY
CASE WHEN @Sort = 0 THEN u.[Id] END DESC,
CASE WHEN @Sort = 1 THEN u.[Id] END ASC,
CASE WHEN @Sort = 2 THEN u.[LastName] END ASC,
CASE WHEN @Sort = 3 THEN u.[LastName] END DESC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

What is the reason now for the slowdown even though the same field is being used for the order? (ID DESC?)

Is there a way to make this perform better?

I have tried in SQL Server 2016 and SQL Azure.

EDIT:
Execution plan/live stats without case ordering:

Execution plan/live stats with case ordering:

Any calculation or function, when applied to an index key column, will prevent SQL Server from using that index.

In your case, ORDER BY Id DESC can make use of an ordered scan of the Clustered Index on your table, as Id is the key column on that index. That means, only 20 rows have to be read.

Once you apply the CASE statement, SQL Server cannot use that index anymore and instead has to scan the entire row set and execute (very expensive) sorting on all rows to find the 20 rows you are interested in.

One of the solutions is to use dynamic SQL to include the appropriate ORDER BY without any calculations (as Lamak suggested in the comments above) or to just write the query 4 times and use an IF statement to decide which one to execute.


PostgreSQL Backup Information How do you get the Backup History for a PostgreSQL database from within the database?
for example:
in SQL Server you look into msdb.dbo.backupset
in Oracle you check v$rman_output
is there a similar way here
Thanks

A PostgreSQL backup leaves no trace in the database. If you use WAL archiving, you will get a *.backup file for each online file system backup in the WAL archive directory, but that's all.
You best use a third-party backup solution like pgBackRest, which will provide such information.


Pass array of mixed type into stored FUNCTION I am preparing procedure, that is basically finishing payment. It takes all info as arguments and do all inserts and stuff in one transaction.

the problem here is, that one argument is list of n arrays (basically list of items that were bought), so I have to make my FUNCTION eat array of arrays.

The solution given by Erwin Brandstetter from question Use array of composite type as function parameter and access it is basically what I need, except one thing. My target table has to have also column: id SERIAL PRIMARY KEY

I have been trying to modify it slightly:

This is target table:

CREATE TABLE cashreg_journal (
  id SERIAL PRIMARY KEY,
  gid INTEGER NOT NULL,
  device_id TEXT NOT NULL,
  item TEXT NOT NULL,
  vat_percentage INTEGER NOT NULL,
  vat_sum INTEGER, price NUMERIC);

This is function:

CREATE OR REPLACE FUNCTION test_insert(daco TEXT, VARIADIC _journal_arr cashreg_journal[]) RETURNS TEXT
  LANGUAGE plpgsql
  AS $function$

DECLARE

  jr cashreg_journal;

BEGIN

  FOREACH jr IN ARRAY _journal_arr LOOP
    INSERT INTO cashreg_journal(gid, device_id, item, vat_percentage, vat_sum, price)
    SELECT jr.*;

  END LOOP;

  RAISE NOTICE 'Daco: %', daco;

  RETURN 'Saved';

END;
$function$;

but it is not working this way:

SELECT * FROM test_insert('Test daco', '(1,000-111,Test-P,20,20,100)', '(1,000-111,Test-F,10,10,100)');
ERROR:  invalid input syntax for integer: "000-111"
LINE 1: SELECT * FROM test_insert('Test daco', '(1,000-111,Test-P,20...
                                               ^

so it seems, that I do not fully understand how it works and my mind ways are not right.

Could anybody please give me some hint, to point me in the right direction? :)

Edit:
I am working on many machnes, however the postgres version is always 9.4, 9.5 or 9.6.
I have also forget to mention that what I like about given solution is, that I do not need to create new type. Which I try to avoid.

Following the link you have supplied and carefully reading the good answers of a_horse_with_no_name and Erwin Brandstetter I've tried to solve this question.

Use array of composite type as function parameter and access it

First I've created a composite TYPE:

CREATE TYPE cr_journal AS (
  gid INTEGER,
  device_id TEXT,
  item TEXT,
  vat_percentage INTEGER,
  vat_sum INTEGER,
  price NUMERIC);

Then I've slightly edited your function:

CREATE OR REPLACE FUNCTION test_insert(daco TEXT, _journal_arr  cr_journal[]) RETURNS TEXT
  LANGUAGE plpgsql
  AS $function$

DECLARE

    cj cr_journal;

BEGIN

    FOREACH cj IN array _journal_arr LOOP

        INSERT INTO cashreg_journal
            (gid, device_id, item, vat_percentage, vat_sum, price)
        SELECT cj.*;

    END LOOP;

  RAISE NOTICE 'Daco: %', daco;

  RETURN 'Saved';

END;
$function$;

And the last step, you should modify your select sentence, instead of SELECT * FROM function() you should call it as SELECT function() and you should explicitly cast the array as an array of type cr_journal[] in this way:

SELECT test_insert('Test daco',
                   (ARRAY[(1, '000-111', 'Test-P', 20, 20, 100),
                          (1, '000-111', 'Test-F', 20, 10, 100)])::cr_journal[]);

returns: Saved

Now you can check it:

select * from cashreg_journal;

And this is the result:

id | gid | device_id | item   | vat_percentage | vat_sum | price
-: | --: | :-------- | :----- | -------------: | ------: | ----:
 1 |   1 | 000-111   | Test-P |             20 |      20 |   100
 2 |   1 | 000-111   | Test-F |             20 |      10 |   100

db<>fiddle here


How do you manage a list for particular object. for eg. user comments on specific object(user post etc.) I am new to database and web application development.  I want to know how to store a list for a particular object in a database.  For example, user comments for a particular post(object), or the friends or followers list of a particular user.

Normally we save data in the form of row

|user_id  | user_name | use_password | etc | etc
|123      | neha      | 123          | ... | ...

but here you cannot save them in rows because its a list:

|post_142524 | comment1 | comment2 | comment3 | etc...

How do you store comments?  I can't think of creating new columns for each comment. and I can't create a table which stores data column-wise like this:

|post_142524|
|comment1   |
|comment2   |
|comment3   |
|comment4   |
|comment5   |
etc...

I want to know how to manage lists for particular object.  I've searched around but, I think my search terms were not perfect to get what I am looking for, as I don't even know what term to use to search for this problem.  Any help would be appreciated!

As the comments indicate, you need to understand normalization.  Without too much theory, when you want a list for a particular object in a database here are some possibilities:

Create a new column for each additional list item.  If your list has a small concrete length this may be a viable option, however for most lists n items today becomes n+1 tomorrow and your table begins to have an unmanageable number of columns that require extra coding for joining and filtering.
Create a new table for each additional list.  If the lists are independent then this is the way to go, however, if the lists are the same, then creating and maintaining a new list table for each and every object becomes unmanageable.  Additionally, queries crossing objects require UNIONs on multiple tables.  These queries could become lengthy and have to be modified with each additional object.
Create a normalized table for this type of list.  If the list structure is the same for each object then a single table can be used to store any number of list items for any number of objects.  The tables definition should be defined and scoped.

For your particular question you have defined the list item as a comment, so you will simply need additional columns to indicate what object (post?) it goes with and what comment number it is.  You may also want to have columns indicating who made the comment, when it was made, and from where it was made.

Here is how this could look:

Post    CommentNumber  Comment
------  -------------  --------------------------
098763  1              Comment 1 for post 098763.
...
142525  1              Comment 1 for post 142525.
142525  2              Comment 2 for post 142525.
...
200526  1              Comment 1 for post 200526.


ERROR unknown variable > 'keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv' Env: Ubuntu 18.04.5 LTS + MySQL 5.7.32
I have a KMIP server working and tested. I am now trying to configuring Keyring to enable symmetric key encryption on my database
Per official docs, I added the following lines to /etc/mysql/my.cnf
[mysqld]
early-plugin-load=keyring_file.so
keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv

restarting mysql:
uadmin@Linux01:/var/keyring$ sudo service mysql restart
[sudo] password for uadmin:
Job for mysql.service failed because the control process exited with error

code.
See "systemctl status mysql.service" and "journalctl -xe" for details.
> -- Unit mysql.service has finished shutting down. Nov 18 08:53:12 Linux01 systemd[1]: Starting MySQL Community Server...
> -- Subject: Unit mysql.service has begun start-up
> -- Defined-By: systemd
> -- Support: http://www.ubuntu.com/support
> --
> -- Unit mysql.service has begun starting up. Nov 18 08:53:12 Linux01 mysql-systemd-start[117506]: ERROR: Unable to start MySQL server: Nov
> 18 08:53:12 Linux01 mysql-systemd-start[117506]:
**> 2020-11-18T13:53:12.240218Z 0 [ERROR] unknown variable
> 'keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv' Nov 18**
> 08:53:12 Linux01 mysql-systemd-start[117506]:
> 2020-11-18T13:53:12.242657Z 0 [ERROR] Aborting Nov 18 08:53:12 Linux01
> mysql-systemd-start[117506]: Please take a look at
> https://wiki.debian.org/Teams/MySQL/FAQ for tips on fixing common
> upgrade issues. Nov 18 08:53:12 Linux01 mysql-systemd-start[117506]:
> Once the problem is resolved, restart the service. Nov 18 08:53:12
> Linux01 systemd[1]: mysql.service: Control process exited, code=exited
> status=1 Nov 18 08:53:12 Linux01 systemd[1]: mysql.service: Failed
> with result 'exit-code'. Nov 18 08:53:12 Linux01 systemd[1]: Failed to
> start MySQL Community Server.

I figured this out. This feature is only available in ENT version of mysql and I am using community ...


audit /track changes I want to track changes on SQL Server 2012, and save it for 5 days.

I want get the time that each insert/update/delete operation occurred on the SQL Server and save the new insert line(on insert operaion)/ old+new value(for update operation) / removed line(for delete operation).

I've searched a lot, but I can't find a solution for it.

If you require change tracking across your entire database, you might consider implementing SQL Server Change Tracking.

If you want to track changes to a single table, you might consider rolling your own solution, as in the example I've created below:

The sample table we want to track changes made against:

CREATE TABLE dbo.SomeData
(
    ID int NOT NULL
        CONSTRAINT PK_SomeData
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeVal varchar(100) NOT NULL
    , SomeDate datetime NOT NULL
);

The table where we'll keep track of changes:

CREATE TABLE dbo.SomeData_Changes
(
    ChangeID bigint NOT NULL
        CONSTRAINT PK_SomeData_Changes
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ChangeGroup int NOT NULL
    , ChangeDate datetime
        CONSTRAINT DF_SomeData_Changes_ChangeDate
        DEFAULT ((GETDATE()))
    , OpType varchar(1) NOT NULL
    , ID int NOT NULL
    , SomeVal varchar(100) NOT NULL
    , SomeDate datetime NOT NULL
);

The change-tracking infrastructure we need includes a sequence to group changes together:

CREATE SEQUENCE dbo.SomeData_Changes_Group
AS int
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 1000;
GO

This is a trigger that will track all changes made to the SomeData table:

CREATE TRIGGER SomeData_ChangeTracker
ON dbo.SomeData
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Group int;
    SET @Group = NEXT VALUE FOR dbo.SomeData_Changes_Group;

    IF EXISTS (
        SELECT 1
        FROM deleted
        )
    BEGIN
        INSERT INTO dbo.SomeData_Changes (OpType, ChangeGroup, ID, SomeVal, SomeDate)
        SELECT 'D'
            , @Group
            , d.ID
            , d.SomeVal
            , d.SomeDate
        FROM deleted d
    END

    IF EXISTS (
        SELECT 1
        FROM inserted
        )
    BEGIN
        INSERT INTO dbo.SomeData_Changes (OpType, ChangeGroup, ID, SomeVal, SomeDate)
        SELECT 'I'
            , @Group
            , i.ID
            , i.SomeVal
            , i.SomeDate
        FROM inserted i
    END
    PRINT N'In trigger';
END
GO

Here, we test the change tracker:

INSERT INTO dbo.SomeData (SomeVal, SomeDate)
VALUES ('test row 1', GETDATE());

SELECT *
FROM dbo.SomeData_Changes;

╔══════════╦═════════════╦═════════════════════════╦════════╦════╦════════════╦═════════════════════════╗
║ ChangeID ║ ChangeGroup ║       ChangeDate        ║ OpType ║ ID ║  SomeVal   ║        SomeDate         ║
╠══════════╬═════════════╬═════════════════════════╬════════╬════╬════════════╬═════════════════════════╣
║        1 ║           1 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
╚══════════╩═════════════╩═════════════════════════╩════════╩════╩════════════╩═════════════════════════╝

An update:

UPDATE dbo.SomeData
SET SomeVal = 'Test Row 1'
WHERE ID = 1;

SELECT *
FROM dbo.SomeData_Changes;

╔══════════╦═════════════╦═════════════════════════╦════════╦════╦════════════╦═════════════════════════╗
║ ChangeID ║ ChangeGroup ║       ChangeDate        ║ OpType ║ ID ║  SomeVal   ║        SomeDate         ║
╠══════════╬═════════════╬═════════════════════════╬════════╬════╬════════════╬═════════════════════════╣
║        1 ║           1 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
║        2 ║           2 ║ 2018-03-02 10:17:21.710 ║ D      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
║        3 ║           2 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ Test Row 1 ║ 2018-03-02 10:17:21.703 ║
╚══════════╩═════════════╩═════════════════════════╩════════╩════╩════════════╩═════════════════════════╝

The above output shows ChangeGroup "2", which is the update.  It consists of both a "delete" and an "insert", which is how many table updates are actually accomplished behind the scenes in SQL Server.

A delete:

DELETE
FROM dbo.SomeData
WHERE ID = 1;

SELECT *
FROM dbo.SomeData_Changes;

╔══════════╦═════════════╦═════════════════════════╦════════╦════╦════════════╦═════════════════════════╗
║ ChangeID ║ ChangeGroup ║       ChangeDate        ║ OpType ║ ID ║  SomeVal   ║        SomeDate         ║
╠══════════╬═════════════╬═════════════════════════╬════════╬════╬════════════╬═════════════════════════╣
║        1 ║           1 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
║        2 ║           2 ║ 2018-03-02 10:17:21.710 ║ D      ║  1 ║ test row 1 ║ 2018-03-02 10:17:21.703 ║
║        3 ║           2 ║ 2018-03-02 10:17:21.710 ║ I      ║  1 ║ Test Row 1 ║ 2018-03-02 10:17:21.703 ║
║        4 ║           3 ║ 2018-03-02 10:17:21.713 ║ D      ║  1 ║ Test Row 1 ║ 2018-03-02 10:17:21.703 ║
╚══════════╩═════════════╩═════════════════════════╩════════╩════╩════════════╩═════════════════════════╝

The delete is ChangeGroup 3 above.

Cleanup:

IF OBJECT_ID(N'dbo.SomeData', N'U') IS NOT NULL
BEGIN
    DROP TRIGGER SomeData_ChangeTracker;
    DROP TABLE dbo.SomeData;
END
IF OBJECT_ID(N'dbo.SomeData_Changes', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.SomeData_Changes;
    DROP SEQUENCE dbo.SomeData_Changes_Group;
END
GO

You could trim rows from the change-tracking table using a SQL Server Agent Job that performs the following every day at midnight:

DELETE
FROM dbo.SomeData_Changes
WHERE dbo.SomeData_Changes.ChangeDate < DATEADD(DAY, -5, GETDATE());


Security update failed : The baseline msi is not installed Yesterday we had to apply a security update for SQL Server 2008 SP4(
Current version: 10.0.6000.29) (from KB3045311 and MS15-058) and it failed in both the nodes of a Windows failover cluster and both the nodes are of the same version. The log files only say this:

  2015-10-21 02:26:29 Slp: Patch Id KB3045311_sql_engine_core_inst_Cpu64 - NotInstalled on the baseline msi package sql_engine_core_inst_Cpu64.  Detail description of this patch package is: PatchId=KB3045311_sql_engine_core_inst_Cpu64 PatchVersion=10.4.6241.0 BaselinePackageId=sql_engine_core_inst_Cpu64 BaselineVersion=10.4.6000.29; PatchFileName=sql_engine_core_inst.msp PatchCode={511E22C9-E550-4C68-994B-AA3F388A4E2F}
  2015-10-21 02:26:29 Slp: Patch Id: KB3045311_sql_engine_core_inst_Cpu32 - The baseline msi is not installed. The patch package is ignored.
  [...many more of these for different patchIds and clsids...]

The application event logs has this:

  Product: Microsoft SQL Server 2008 Database Engine Services - Update
   '{511E22C9-E550-4C68-994B-AA3F388A4E2F}' could not be installed. Error
   code 1642.

However, the security update for SQL Server 2012 did go through fine which was done at the same time.

Any thoughts about why the SQL Server 2008 SP4 security update is failing?

Edit: I reviewed this a bit further and another cause of this issue has been the Windows Installer cache.  That seems to create a 'file not found' error when looking for the copy of the msi/msp package that's copied into the Windows directory with the update GUID.  We're not seeing that here, but I think it's because we're not seeing the actual log file for the KB.  Aaron's point was good to bypass Windows Update, but also check to see if everything actually exists where it is supposed to if that doesn't work.
Check here for that solution to this issue.  Again, we will know much better when we can get the actual log.

This has came up a few times in prior versions of MS SQL Server.  This blog shows a good way to actually troubleshoot and resolve the issue. His solution of copying a exe from the other node and replacing it in the binn folder then restarting seems too hacky for me.  I like the SQLGuru suggestion of just reinstalling the prior SP.  That might give you some good results.

This problem can be caused by several issues such as using the wrong patch (x64 vs x32, wrong sp cu, etc.) and other scenarios such as the one listed where the version of the exe for whatever reason isn't what it is expecting, or a entry is not found for that patch in the registry, or God knows what else.  I don't.

The best way to really see what is the root underlying issue would be to review the log files for that particular KB.  It should be in the same section as your other log file you pasted.  From there you should be able to note if the actual patch is the wrong one or out of order, if you need to just reinstall the prior SP and then retry, or if there's another error you'll need to address.  The actual log for those should be named something like sqlversion_KB#.log


Question on compression for very large tables in sql server I have a confusion on how this compression feature works in SQL server:
For quite some large tables , ones with over TB, we recently implemented PAGE level compression.
Example of how it was done:-
Use [DBname]

ALTER TABLE dbo.sampletable REBUILD PARTITION =1  WITH (DATA_COMPRESSION = PAGE);

ALTER TABLE dbo.sampletable REBUILD PARTITION =2  WITH (DATA_COMPRESSION = PAGE);

and so on...
The tables in question were all partitioned table.
When checking the details of compression i see at table level ( Clustered index ones) it shows compression =PAGE but for existing non clustered indexes , compression says = NONE
Queries i have used are from here and here
I am not sure what should i interpret from above if compression is enabled at table level or needs to be enabled at index level as well?
Is there space and performance benefit in case we should also consider compressing NC indexes as well? Basically what would be my ideal case here to go with compressing them as well?
Thanks

It is impossible for us to answer whether you want to compress your indexes or not. Only you have access to your data, server and queries. Considerations includes:
Page compression is pretty expensive to crack ("decompress"). Pages lives in memory in compressed form, meaning that every time a page is accessed (including in memory), it has to be cracked. You can probably imagine that pages in an index can be navigated (cracked) very frequently.
Continuing on above reasoning. If the data tend to live in cache (even in uncompressed form), then you will notice more of the CPU overhead. But if the data tend to live on disk, then the CPU overhead might drown compared to the potential I/O savings.
People tend to discard row compression as an option. In some cases it can give a pretty decent compression ratio, and it has close to none overhead.


Can I manually reset the "internal status flag for a full database backup"? We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day.

This worked last weekend, however this weekend the differential backup failed saying that

  Message

  Executed as user: username. ... 9.00.3042.00 for 32-bit
  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
  Started:  12:30:00 AM
  Progress: 2013-01-21 00:30:01.69
  Source:
  {450389BA-54C2-4892-9CD0-0126CA9B0ED8}      Executing query "DECLARE
  @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete
  End
  Progress
  Error: 2013-01-21 00:30:02.11     Code: 0xC002F210
  Source: Back Up Database (Differential) Execute SQL Task
  Description: Executing the query "BACKUP DATABASE [MyDatabase] TO
  DISK = N'E:\Database Backups\MyDatabase_backup_201301210030.bak' WITH
  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME =
  N'MyDatabase_backup_20130121003002', SKIP, REWIND, NOUNLOAD,  STATS =
  10  " failed with the following error: "Cannot perform a differential
  backup for database "MyDatabase", because a current database backup
  does not exist. Perform a full database backup by reissuing BACKUP
  DATABASE, omitting the WITH DIFFERENTIAL option.   BAC...  The package
  execution fa...  The step failed.

The job that runs the full database backup ran successfully, and I can view the backup using the query found here, so I am not sure why it is telling me "a current database backup does not exist".

I found this MSDN page which suggests that the cause of the problem is

  This problem may occur when the internal status flag for a full
  database backup does not reset after you create the full database
  backup.

I'm not able to install the hotfix right away, and doing another full database backup is not an option right now, so I was looking to try manually resetting the internal status flag for a full database backup.

Is there a way to manually reset this flag so that differentials can successfully be ran for the rest of the week? Or to set the base backup to an existing full backup that exists in msdb.dbo.BackupSet/msdb.dbo.BackupMediaFamily?

I'm using SQL Server 2005

Your primary goal here is to ensure your ability to restore the database.  As long as you have the complete chain of your log backups from the last full to now, you can perform a point in time recovery and the error is merely an inconvenience.  Differential backups are not required for full point in time recovery, just provide you with a shortcut for larger time frames.  However, in the absence of your differentials, you'll need all the log backups to cover the gap.

Otherwise, the only way to properly reset this is to execute another actual full backup.  I would recommend executing this as soon as possible which, depending on the size of your database, should be something that could be accomplished overnight in place of your normal differential.

Also, I would strongly recommend that you apply Service Pack 4 for SQL 2005 to make sure you have the most current hotfixes/updates for your platform.


Postgres - how to avoid unnecessary comparisons inside a WHERE with a JOIN? Background

We're running this query:

SELECT COUNT(*) AS "__count"
FROM "xfiler_document"
INNER JOIN "xfiler_tx" ON ("xfiler_document"."tx_id" = "xfiler_tx"."id")
WHERE (
    "xfiler_document"."added_on" <= '2015-06-28T23:59:59.999999-04:00'::timestamptz
    AND "xfiler_tx"."company_id" = 1
    AND "xfiler_document"."added_on" >= '2015-06-01T00:00:00-04:00'::timestamptz
)

The result of EXPLAIN ANALYZE is @ https://explain.depesz.com/s/voMo

The table is large (+30 million records), so we've run into a performance problem with a report we're running (this can be seen at the URL above; +25 second query).

Question

It seems to me, based on the analyze results, that the performance issue here is due to the fact that Postgres is comparing the added_on values (step 4), and then re-checking them for some reason (step 3) for every row from the query's added_on time frame, rather than only checking the 18,515 rows that apparently match the company_id comparison in step 7.

Firstly, am I understanding / explaining the performance issue correctly?

Secondly, is there a way to solve this (other than denormalizing company_id)?

Database

Postgres 9.6.1

xfiler_document

       Column        |            Type             |                          Modifiers
---------------------+-----------------------------+--------------------------------------------------------------
 id                  | integer                     | not null default nextval('xfiler_document_id_seq'::regclass)
 tx_id               | integer                     | not null
 doc                 | character varying(255)      |
 added_on            | timestamp with time zone    | not null

Indexes:
    "xfiler_document_pkey" PRIMARY KEY, btree (id)
    "xfiler_document_tx_id" btree (tx_id)
    "xfiler_document_added_on" btree (added_on)
Foreign-key constraints:
    "xfiler_document_tx_id_fkey" FOREIGN KEY (tx_id) REFERENCES xfiler_tx(id) DEFERRABLE INITIALLY DEFERRED

xfiler_tx

           Column           |           Type           |                       Modifiers
----------------------------+--------------------------+--------------------------------------------------------
 id                         | integer                  | not null default nextval('xfiler_tx_id_seq'::regclass)
 name                       | character varying(255)   | not null
 company_id                 | integer                  | not null

Indexes:
    "xfiler_tx_pkey" PRIMARY KEY, btree (id)
    "xfiler_tx_company_id" btree (company_id)
    "xfiler_tx_name" btree (name)
Foreign-key constraints:
    "xfiler_tx_company_id_fkey" FOREIGN KEY (company_id) REFERENCES company_company(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "xfiler_document" CONSTRAINT "xfiler_document_tx_id_fkey" FOREIGN KEY (tx_id) REFERENCES xfiler_tx(id) DEFERRABLE INITIALLY DEFERRED

The plan entry Recheck Cond: indicates what conditions will be used for the recheck, should a recheck be necessary.  This will always be listed, even if no recheck is actually necessary at run time.  (You can tell this because the Recheck Cond shows up even if you do a regular EXPLAIN, not an EXPLAIN ANALYZE, so this is part of the planning process, not the execution process.)

In your situation, all Heap Blocks were exact, meaning no recheck was necessary.

The real explanation for the slowness is probably that the Bitmap Heap Scan needs to read a lot of data from disk in order to find the rows it needs.  You could check this by turning track_io_timing on and running EXPLAIN (ANALYZE, BUFFERS).

If that is indeed the issue, one solution would be to CLUSTER xfiler_document ON xfiler_document_added_on so that the data for a given date range are closer together on disk.

If your data is on a RAID, then increasing effective_io_concurrency could help.


Union of results of all database query to also include database name I'm hoping someone can help.  I have a number of servers each with multiple databases on it.  All of the databases have a table I want to query with a very simple 'select count(*) from section'.  I want to get the results plus the database name all collated together (i.e. not as separate query results that I can't copy out in one quick click). So results like:

**DB Name** **Number of Sections**

Name1  10

Name2 20

Name3 11

Name4 9

Name5 20

So far I've got two queries as below:

This query gives me a list of the results as one query altogether, however it doesn't give me the DB Name that the result relates to and I can't suss out how to add it.

DECLARE @sql varchar(max);

SELECT @sql = Coalesce(@sql + ' UNION ALL ', '') + 'SELECT count(*) as ''Number of Sections'' FROM ' + QuoteName(name) + '.dbo.section'
FROM   sys.databases
where database_id > 4
;

EXEC (@sql);

This query gives me the exact results I want but they are all separate queries so I'd have to go through and copy each one manually to collate the results.

DECLARE @command varchar(1000)

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? SELECT ''?'', COUNT(*) FROM section END'

EXEC sp_MSforeachdb @command

If anyone can give me an idea on how I can get the output I want it would be much appreciated.

Just add the DBName to your first query:

DECLARE @sql varchar(max);

SELECT @sql = Coalesce(@sql + ' UNION ALL ', '') +
        'SELECT '''+name+''' AS DBName, count(*) as ''Number of Sections'' FROM ' + QuoteName(name) + '.dbo.section' + CHAR(13)
FROM   sys.databases
where database_id > 4;

--PRINT @sql
EXEC (@sql);

Generally you want to avoid sp_msforeachdb.  It has some well known bugs.

https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-sp_msforeachdb

Edit: If you want to run this on multiple servers you could write a quick SSIS package that loops through an instance table and collects the data to a central location.  I did a walkthrough on the following 3 posts:

Using a fixed list and a for each loop that changes a connection manager with expressions.
Using the package to store data to a central location.
Touching up the package and expanding it to use an instance table to store the list of servers.

Combining all of that into this answer would be a bit long but here are the basics:

Create an instance table on one of your instances.
Create a table to store the data you want to collect (probably the same instance).
In an SSIS package create a SQL Task that queries the instance table and stores the results in an object variable.
Create a for each loop that loops through the variable.
In the for each loop change a variable to be equal to the current instance name from the object variable.
Create a connection manager that uses an expression to change the server name property to the contents of the instance variable.
In the for each loop create a data flow

The data flow source uses the connection manager with the expression.
Put the query that collects your data in the source.
Set the destination to the table you created in step 2.


Oracle 11g - drop maxvalue partition, keep data I have some tables with data like this:

 CREATE TABLE "PARTTEST4"
   ("year" number(4,0) NOT NULL ENABLE)
  PARTITION BY RANGE ("year")
 (PARTITION "P_OLD" VALUES LESS THAN (2010),
  PARTITION "P_2020"   VALUES LESS THAN (2020),
  PARTITION "P_MAX"    VALUES LESS THAN (MAXVALUE));

insert into parttest4 values (2005);
insert into parttest4 values (2015);
insert into parttest4 values (2025);

...in very simplified terms. Some of the tables also have some partitioned global/local indexes. I wish to drop the maxvalue partition (to use SET INTERVAL instead) but:

DROP PARTITION --causes data loss. Unacceptable
MERGE PARTITION --violates "VALUES LESS THAN (2020)" if I try to merge the P_2020 + P_MAX

What should I do? Split the P_MAX? What precautions should I take?

Thanks

Split above current maximum value, drop the empty MAXVALUE partition, then convert to interval partitioning:

alter table parttest4 split partition p_max at (2030) into (partition p_2030, partition p_max) update global indexes;
alter table parttest4 drop partition p_max;
alter table parttest4 set interval (10);


Sum of decimal values that come in a string Is there an easy way to get the sum of all decimal values in a string column.

Example:

"ABC $3.25, DEF $2.50, HIJ2 $0.25"
"ABC1 $3.25, DEF $2.50, HIJ $0.25, KLM $4.50"

I want to add only the values after the $ and before a ,.

Any help is highly appreciated.

If your SQL version is 2016 or later, you could try to use STRING_SPLIT function.
Against earlier version, there are also lots of impletmentations of string split by searching GG.

Below is my example for SQL 2016+:

DECLARE @v nvarchar(256) = N'ABC $3.25, DEF $2.50, HIJ2 $0.25,ABC1 $3.25, DEF $2.50, HIJ $0.25, KLM $4.50'
SELECT  TRIM(value), RIGHT(value, LEN(value)-CHARINDEX('$',value))
FROM    STRING_SPLIT(@v,',')
SELECT  SUM(CONVERT(decimal(10,2),RIGHT(value, LEN(value)-CHARINDEX('$',value))))
FROM    STRING_SPLIT(@v,',')


SELECT in trigger function in two tables My table and trigger in  PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:
CREATE TABLE measurements (
        measurement_id SERIAL PRIMARY KEY NOT NULL,
        measurement_size_in_bytes INTEGER NOT NULL
    );

CREATE TABLE file_headers (
        header_id SERIAL PRIMARY KEY NOT NULL,
        measurement_id INTEGER NOT NULL,
        file_header_index_start INTEGER,
        file_header_index_end INTEGER
    );

CREATE TRIGGER measurement_ids AFTER INSERT
        ON measurements FOR EACH ROW
        EXECUTE PROCEDURE ins_function('SELECT measurement_id FROM measurements
        ORDER BY measurement_id desc limit 1;', 1, 666 );

where I assumed that the datatype of the SELECT is INTEGER since SERIAL but it is apparently false because I get the error message from this command which starts the trigger:
INSERT INTO measurements (measurement_size_in_bytes) VALUES (888);`

ERROR:  invalid input syntax for integer: "SELECT measurement_id FROM measurements ORDER BY measurement_id desc limit 1;"
CONTEXT:  PL/pgSQL function ins_function() line 10 at assignment

Edit
ins_function() and edits based on @a_horse_with_no_name and @Joishi's comments:
CREATE OR REPLACE FUNCTION ins_function() RETURNS TRIGGER AS $$
    --
    -- Perform AFTER INSERT operation on file_header by creating rows with new.measurement_id, new.file_header_index_start and new.file_header_index_end.
    --
DECLARE
    measurement_id              INTEGER;
    file_header_index_start     INTEGER;
    file_header_index_end       INTEGER;
BEGIN

    SELECT a.measurement_id INTO measurement_id from measurements a ORDER BY measurement_id desc limit 1;
    file_header_index_start := TG_ARGV[0];
    file_header_index_end := TG_ARGV[1];

    IF TG_OP = 'INSERT' THEN
        INSERT INTO file_headers (measurement_id, file_header_index_start, file_header_index_end)
        VALUES (measurement_id, file_header_index_start, file_header_index_end);
        RETURN NEW;
    END IF;

    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$$ LANGUAGE plpgsql;

--
-- Function and trigger on INSERT.
--
CREATE TRIGGER measurement_ids AFTER INSERT
    ON measurements FOR EACH ROW EXECUTE PROCEDURE ins_function(1, 666);

I get now no error but the output is wrong: no INSERT seen in the table file_headers while successfully in the table measurements.
Output of @ErwinBrandstetter's answer
So I started to think about casting from TEXT to INT but this should be so basic operation, since TG_ARGV[] is a datatype of text. One unsuccessful try is format('SELECT $1.%I', TG_ARGV[0]).
The regclass could work as you describe here in insaft_function()
SELECT NEW.measurement_id, TG_ARGV[0]::regclass, TG_ARGV[1]::regclass;

Why are there no successful INSERTs into the table file_headers?

You have an unresolved naming conflict.

You must be using an old version of Postgres without declaring it. Or you are operating with non-default configuration setting.

Here you declare a variable named measurement_id:

DECLARE
    measurement_id              INTEGER;

It's a folly to use ambiguous variable names to begin with. If you do it anyway, you must know what you are doing. I make it a habbit to prepend variable names with an underscore unlike column names, like _measurement_id.

The later SELECT statement is ambiguous:

ORDER BY measurement_id

This would raise an error message in modern PostgreSQL with default configuration. Per the documentation:

  By default, PL/pgSQL will report an error if a name in a SQL statement
  could refer to either a variable or a table column.

And:

  To change this behavior on a system-wide basis, set the configuration
  parameter plpgsql.variable_conflict to one of error, use_variable, or
  use_column (where error is the factory default). This parameter
  affects subsequent compilations of statements in PL/pgSQL functions,
  but not statements already compiled in the current session. Because
  changing this setting can cause unexpected changes in the behavior of
  PL/pgSQL functions, it can only be changed by a superuser.

In Postgres older than 9.0 this would be resolved to mean the variable. Per the documentation

  In such cases you can specify that PL/pgSQL should resolve ambiguous
  references as the variable (which is compatible with PL/pgSQL's
  behavior before PostgreSQL 9.0)

Bold emphasis mine.
This would result in arbitrary results, since the sort order is now undetermined.

Audited Function

CREATE OR REPLACE FUNCTION insaft_function()
   RETURNS TRIGGER AS
$func$
DECLARE
   _measurement_id          integer;
   _file_header_index_start integer := TG_ARGV[0]::int;
   _file_header_index_end   integer := TG_ARGV[1]::int;
BEGIN

   SELECT a.measurement_id   INTO _measurement_id
   FROM   measurements a
   ORDER  BY a.measurement_id DESC  -- you had ambiguity here!
   LIMIT  1;

   IF TG_OP = 'INSERT' THEN  -- noise if only used in AFTER INSERT trigger
      INSERT INTO file_headers (measurement_id, file_header_index_start
                                              , file_header_index_end)
      VALUES (_measurement_id, _file_header_index_start, _file_header_index_end);
   END IF;

   RETURN NULL; -- result is ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;

Note how I named it insaft_function(), since this is only to be used in an AFTER INSERT trigger.

Trigger:

CREATE TRIGGER insaft_measurement_ids
AFTER INSERT ON measurements
FOR EACH ROW EXECUTE PROCEDURE insaft_function(1, 666);

But for the provided setup, you can radically simplify the function:

CREATE OR REPLACE FUNCTION insaft_function()
   RETURNS TRIGGER AS
$func$
BEGIN
   INSERT INTO file_headers (measurement_id, file_header_index_start
                                           , file_header_index_end)
   VALUES (NEW.measurement_id, TG_ARGV[0]::int, TG_ARGV[1]::int);

   RETURN NULL;  -- result ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;


SQL Server 2008 R2 not starting after moving model database SQL Server 2008 R2 not starting after moving model database. Any ideas? Error: 945, Severity: 14, State: 2.  Any ideas?
Sql 2008 R2 will not start with trace -f -T3608 or single user mode -m. Need params to start SMS to move Model back.

You probably moved the model database without issuing the required ALTER DATABASE statements. In the errorlog you will find the path where SQL Server expects to find the model database files (data and log) and you can move back the files to that location and start the service again.

After the service has started, you can follow the instructions given here to move the model database to a different location.

Another possible cause could be the SQL Server service account is unable to access the path where the model database files are stored. Again, you can check it in the errorlog.


Meaning of SSMS database icon preceded by scroll with red x SSMS 2008 R2, connecting to a 2008 instance shows the above glyph in SSMS Object Explorer. None of our other instances show that icon. I've moused over it in the vain hopes that I'd get a useful tooltip but to no avail.

I assume it's related to a failure we had this past weekend. Could someone provide a more authoritative response on what that icon means and any action I should direct my DBAs to take to clear it up?

This is documented in BOL under "Administering Servers by Using Policy-Based Management"

  For failed policies, Object Explorer indicates a critical health
  warning as a red icon next to the target and the nodes that are higher
  in the Object Explorer tree.

I've not used policy based management myself so am not entirely clear what this means. Through the UI I couldn't see any way of denoting a policy condition as having any particular severity.

I've made this answer community wiki in case someone more familiar with the subject area wants to expand upon it.


Does EnterpriseDB support Oracle RAC to Postgres replication? Does EnterpriseDB support Oracle RAC to Postgres replication?

The difference between Oracle RAC and Postgres cluster architectures does not in itself prevent you from setting up replication between an Oracle database (RAC or no RAC) and a Postgres database (Advanced Server or not).


How is data of multi valued attribute say phone number of person relation is stored in table? I have searched for the answers referencing my books as well as web but i could not find one. So here i am.
I am not sure what this question is trying to ask so help if anyone could.

When you say a multi-valued attribute, for a phone number for example, you mean because the phone number has multiple parts to it - area code, suffix, and prefix? If so, how you store it just depends on your use case for it.
If your use case is to just store it so it can be later recalled and displayed in the application, then storing the whole phone number as is, in a string-based field (e.g. VARCHAR in SQL Server) is sufficient.
If you need to be able to query about different parts of the phone number, then storing it as multiple separate INT fields for each part would be more appropriate and easier to work with, e.g. AreaCode, PhonePrefix, and PhoneSuffix. That way you can index each part of the phone number for efficient querying, and it's easy to query for things like, "who are all the people in area code 631?" by just doing a query of the kind SELECT * FROM People WHERE AreaCode = 631, etc.
It truly just depends on your use cases.


In a CASE Statement, which is faster: "WHEN [field] in (0)" or "WHEN [field] = 0"? I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =.  My gut tells me this isn't as fast, efficient, etc.

For example:

CASE WHEN a.Indicator in ('Yes') then 'something'
     WHEN a.STATUS in (0) then 'something 0'
     WHEN a.STATUS in (1) then 'something 1'
     WHEN a.STATUS in (2) then 'something 2'
     WHEN a.STATUS in (3,4,5,6,7,8) then 'something big'
END

Is it faster to use IN instead of =?

The following construct:

c IN (x, y, z)

Gets extrapolated to:

c = x OR c = y OR c = z

It will logically follow that c IN (x) extrapolates to c = x. Therefore the first comment is absolutely right: you will not see a difference in performance except at the very edge case (where the size of the query text is actually impacted sufficiently by the choice), but even there it is hard to fathom the performance difference being worth mentioning.


Purpose of IDs in a User Table Database In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?

I'd answer with a question: Why do you need a table for a single row?
Regarding the design of the table, any table should have a primary key. I won't discuss that as it's the baseline of any design book. Regarding the way of designing that primary key, there are two schools of thought:

one that makes the PK as a separate column, usually self generated, like a GUID or and auto increment INT (in your case a separate uniqueidentifier column);
one that makes the PK as a column (or set of columns) internal to the table (in your case would be a username or email or SSN, whatever makes that user unique) that uniquely identify a record.

To what line you adhere, it's only a matter of taste.


Why does my server have thousands of "HIDDEN ONLINE" schedulers? We have a SQL Server 2012 instance with 16 CPU cores.  When looking in sys.dm_os_schedulers we see the expected 16 rows that are "VISIBLE ONLINE", one per core.  The error log shows the following standard entry on startup:

SQL Server detected 2 sockets with 8 cores per socket and 8 logical
processors per socket, 16 total logical processors; using 16 logical
processors based on SQL Server licensing. This is an informational
message; no user action is required.

However sys.dm_os_schedulers also contains approx. 1200 rows that are "HIDDEN ONLINE".

The server is not using Availability Groups or Mirroring, and Resource Governor is not enabled.
The problem is that each hidden scheduler appears to be assigned one worker thread, so our monitoring software is constantly alerting that the server is running out of threads, even though queries are running perfectly happily and there does not appear to be any actual impact to performance.
I'm aware that hidden schedulers are only available to internal system processes, but do you know what would cause so many to be shown?

Converting my comment to answer :
Some one might be running undocumented fn_dump_dblog and it is creating a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be reused) until a server restart. It is fixed in 2012 + SP2 though.
Also, you can use this query to find out the creation time of schedulers :
 SELECT s.scheduler_id,  s.status, th.started_by_sqlservr, th.creation_time
FROM sys.dm_os_workers w
JOIN sys.dm_os_schedulers s
ON w.scheduler_address = s.scheduler_address
 JOIN sys.dm_os_tasks t
ON t.task_address = w.task_address
  JOIN sys.dm_os_threads th
ON th.thread_address = w.thread_address
where s.status = 'HIDDEN ONLINE' -- just get the hidden online schedulers for your specific case
ORDER BY s.scheduler_id


MySQL Error 10048 on nested Delphi Queries I'm having a problem with MySQL (Server 5.1.11) interfacing with a Delphi application

The problem occurs when trying to use the application to do a bulk export to Excel.  During the application queries for a list matching projects (Query A), then for each matching project the details of the Project are requested from the database (Query B)

The error that occurs is

Can't Connect to MySQL Server on 'stkitts' (10048)

It generally occurs when trying to reach larger batches.  For example I am able to query the past 3 years (771 projects, 1150 records including details), but expanding to 4 years I error at only 443 records returned.

I've checked that my data is OK, and the error is not associated with specific records.  Repeating a search will break at a different point - although you are unlikely to get results for 4 years back...

The error can occur when reading a field or a new record.  So the returned Excel sheet may or may not have a complete line at the point the error occurred.

My gut feeling is that I am sending too much traffic to the DB server, but I am only using two connections to the database for the above queries (MySQL Administrator confirms that there are only 2 active connections during the export)

Any suggestions?

Tracked it down, and once again it's Delphi's hidden "Autoclone" option that is the culprit.

Set Autoclone to False and the number of queries gets limited and the error is skipped.


What is faster - insert into new table, or delete from existing table? I have a huge DB that I want to scale down, to only look at my area of interest. Initially I wanted to write the records I'm interested in onto a new database and table. However, this is taking for ever, so I'm wondering if it might be faster to delete the records I do not want. I already got a copy of the database, so there would be no problem to delete.

Insert statement:

INSERT INTO NewTable (C1, C2, C3, C4, C5, C6, C7)
SELECT OldTable1.C1, OldTable1.C2, OldTable1.C3, OldTable1.C4, OldTable1.C5, OldTable1.C6, OldTable1.C7
FROM OldTable1
INNER JOIN OldTable2.C5 ON OldTable2.C5 = OldTable1.C5
WHERE OldTable2.C8 = 70

If I would delete I would delete all data from OldTable1 that did not match the inner join and where statement.

It very much depends on the details of your database.

Factors include:

Number of records to be copied/retained;
Whether you're keeping all columns or not; and
Number of indexes on old and new table (as alluded to by @a_vlad).

If I needed to delete most of the records in a table, I have definitely had times where it was faster to copy the records to another table, TRUNCATE the first table, and then copy the records to be saved back to the first table (with all indexes, constraints, etc.; the other table would usually be stripped down to just the raw data).

That said, if you're keeping notably more records than you're deleting, and if you don't have a lot of indexes on the original table, it's entirely possible that deleting would be faster.

NOTE: If you don't need to keep all columns, then the INSERT method is almost certainly your best bet.


I can backup my SQL Server 2008 R2 but can't restore from the very same folder I ran a backup into a folder. The Network Service has full control access to this folder. The backup process didn't work until I granted that account full control.

I try to restore that same file but I get an error

  Operating system error 5 (failed to retrieve text for this error. Reason: 15105).

If I move the file to the instance's actual folder it restores fine, so the file itself is not bad.

Is there something slightly different in the folder rights needed to run a backup versus a restore? What is that difference?  FYI, I am doing all this programmatically using a C# application. To test the above backup/restore, I've used the server management tool.

The user access was done via the C# code, though. Here is the main line:

myDirectorySecurity.AddAccessRule(new FileSystemAccessRule(sqlserviceuser,FileSystemRights.FullControl,AccessControlType.Allow));

I connect to the database using a SQL Server authenticated user, not a Windows user, so I can't add the 'logged in' user as full control to the folder.

found it. turns out the permissions command I was using isn't enough for a restore (it's enough for the backup).  Granting the full access rule on the folder isn't enough - it has to be on "the folder, subfolders and files".

basically two more lines of code:

myDirectorySecurity.AddAccessRule(new FileSystemAccessRule(sqlserviceuser, FileSystemRights.FullControl, InheritanceFlags.ContainerInherit, PropagationFlags.None, AccessControlType.Allow));

myDirectorySecurity.AddAccessRule(new FileSystemAccessRule(sqlserviceuser, FileSystemRights.FullControl, InheritanceFlags.ObjectInherit, PropagationFlags.None, AccessControlType.Allow));


Using -- Can a mysql user own tables? A user can create tables owned by them in Oracle.
Can this be done using MySQL?

There is no "ownership", only permission to read/write/drop/etc.  However, it is possible to have conventions of how permissions are GRANTed that sells a lot like "ownership".

Admin (typically 'root'):

GRANT ALL PRIVILEGES ON *.* TO root@...

"Owners":

GRANT ALL PRIVILEGES ON Leslie_database.* TO Leslie@...
GRANT ALL PRIVILEGES ON Dana_database.* TO Dana@...

That lets Leslie do everything with Leslie_database, but he/she cannot touch Data_database.

Note that the syntax schema.DBO.tablename does not exist in MySQL, only dbname.tablename.

Close enough to "ownership"?

GRANTs can also be done at the "table" level, and even the "column".

You could let Leslie "see" Dana_database:

GRANT SELECT ON Dana_database.* TO Leslie@...


Can DBA see card data - PCI - DSS So just trying to understand what the industry standard is around DBA's being able to see card number.

As per PCI document

  7.1 Limit access to system components and cardholder data to only those individuals whose job requires such access.
  7.2 Establish an access control system(s) for systems components that restricts access based on a user’s need to know, and is set to “deny
  all” unless specifically allowed.

So can DBA's with syadmin access ok in seeing card numbers.  If not how is it done in real world?

No.

Preferably card numbers and similar information should be stored in a Vault, where access can be controlled and no single person have full access.

If that is not possible, and the card numbers needs to be stored in the database, then they should always be stored encrypted.
Many databases have functions that can do the encryption on the database side, although it is best to do it in the application so that the numbers are encrypted before sent over the network.

For instance:

Postgres: pgcrypto module (pgp/raw encryption)
SQL Server: ENCRYPTBY* functions
Oracle: DBMS_CRYPTO package


Database Hardware and Software to handle million requests per second? I'm curious about the necessary hardware and software requirements to support a strong MMORPG. For example, what kind of requirement (both hardware and software) would I need if I were to build a system that can incorporate million requests at the same time and avoid lags? How can I keep the game from glitching by processing the requests efficiently?

I understand it depends on proper programming and database structure. Assuming there is 1 table that will have 2 rows updated for every request and there are a million requests, what kind of system and database would I need to do that flawlessly without lags? What amount of time would it take to process all those requests?

Just wondering what I should expect if I'm going to develop a MMORPG.

I would really like to get your feedback/response on this.

It really isn't a database platform problem. All the major databases and operating systems will do fine provided sufficient hardware with sufficient bandwidth to memory, disk, and network are provided.

All databases are built for this kind of scenario -- that is, where you need to update lots of rows from different clients all the time. This is what indexes and primary keys are used for, and the database is optimized for updating in this manner. (i.e., UPDATE your_table SET your_column=your_value where your_key=12)

The hardware requirements are going to be your biggest issue, and I suspect you will need to think about quite a lot here, including:

Failover (what happens when your main server fails?)
Clustering (You may simply need to have more than one database server to handle your load)
Processors (2? 4? 8? 16? Multi-core? Does the db provide multi-core support that is well optimized?)
Memory (The faster the better, but your chipset must be able to handle the bandwidth as well)
Disk (Faster I/O the better. eSATA/SATA or Fiber, etc.)
Network (You'll need lots of bandwidth to handle this kind of traffic)
Virtualization (Does it make sense to build this as real hardware? Why not as virtual servers in the cloud? Amazon / Rackspace / etc.?)

Thankfully a good majority of the scaling issues are handled either at the hardware or db level. That is, you can start your database now on a slow server and as traffic increases you can adjust accordingly with minimal changes to your code or your db structure. What will change is your hardware and your db configuration. Most database servers will support failover/clustering with little to no change to your existing code or structures. (But be sure to research possible costs and the efficiency thereof first)


Functional dependencies proving method I was having some difficulties when trying to use Armstrong axiom to solve functional dependencies.

R (A,B,C,D,E,F)
Given FD = { A->CD , C->BE, B->F, F->A }
Prove A-> BF

By using closure, for A, I get {A,C,D,B,E,F} so I know A->BF is valid.

So I came out with these to prove using Axiom:

A->CD
CD->BED (Augmentation)
A->BED (Transitivity)
BED->FED (Augmentation)
A->FED (Transitivity)

Then from there, I am lost. Anybody knows how to prove it?

Thanks in advance.

Here is a possible derivation:

 1. A → CD   (given)
 2. C → BE   (given)
 3. CD → BED (by augmentation of 2 with D)
 4. A → BED  (by transitivity of 1 and 3)
 5. B → F    (given)
 6. B → BF   (by augmentation of 5 with B)
 7. BED → B  (by reflexivity)
 8. A → B    (by transitivity of 4 and 7)
 9. A → BF   (by transitivity of 8 and 6)


How to activate shard balancer in sharded MongoDB I have a very simple MongoDB database structure. Also I have 5 shards in it (3 of them are replicasets). But we are testing DB loading and it seems that shard balancing isn't currently enabled.

On mongos I have checked the next:

mongos> sh.getBalancerState()
true
mongos> sh.isBalancerRunning()
false

I can't set BalancerRunning to true. I tried:

sh.startBalancer()

Please help me to start it for all my shards. Thank you.

Basically you have a few misunderstandings here, the first being that the balancer is a load balancer.  It is not - it simply looks to address imbalances in chunk counts on your shards.  That can have the side effect of balancing your traffic out as it moves chunks around, but strictly speaking it is not a load balancer.  It also does not run continuously, rather it runs when there is work to be done and imbalances to address, otherwise it is dormant.

To explain the output you are getting from the commands, let's take them one at a time.  First off, Let's look at what sh.getBalancerState() does (run any function without parentheses in the mongo shell and you get to see the code behind it):

mongos> sh.getBalancerState
function () {
    var x = db.getSisterDB( "config" ).settings.findOne({ _id: "balancer" } )
    if ( x == null )
        return true;
    return ! x.stopped;
}

So, what that command is doing is checking the settings collection in the config DB to determine if the balancer is enabled or not.  If we stop the balancer, we see the setting change:

mongos> sh.stopBalancer()
Waiting for active hosts...
Waiting for the balancer lock...
Waiting again for active hosts after balancer is off...
mongos> sh.getBalancerState()
false

If we flip it back to enabled, we see true returned once more:

mongos> sh.startBalancer()
mongos> sh.getBalancerState()
true

So, sh.getBalancerState() is basically for checking the setting and telling you whether the balancer is enabled or not.  What it does not speak to is whether the balancer is currently actively running (i.e. checking for imbalances, migrating to address any imbalances it finds).  That's where sh.isBalancerRunning() comes in.

However, if the balancer is not currently doing any work, it will not be "running" and so it will return false:

mongos> sh.isBalancerRunning()
false

Hence, let's give it some work to do.  I will re-use my example from this answer and create an imbalance while the balancer is off.  Here is sh.status() and the output of sh.getBalancerState() once I have completed the pre-split:

mongos> sh.getBalancerState()
false
mongos> sh.status()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "version" : 3,
    "minCompatibleVersion" : 3,
    "currentVersion" : 4,
    "clusterId" : ObjectId("53b5d3b5d95df3a66a597548")
}
  shards:
    {  "_id" : "shard0000",  "host" : "localhost:30000" }
    {  "_id" : "shard0001",  "host" : "localhost:30001" }
    {  "_id" : "shard0002",  "host" : "localhost:30002" }
  databases:
    {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
    {  "_id" : "test",  "partitioned" : false,  "primary" : "shard0001" }
    {  "_id" : "users",  "partitioned" : true,  "primary" : "shard0001" }
        users.userInfo
            shard key: { "_id" : 1 }
            chunks:
                shard0001   2049
            too many chunks to print, use verbose if you want to force print

Once I re-enable the balancer, it is going to have plenty of work to do to redistribute those 2049 (empty) chunks evenly across 3 shards, so I will have plenty of opportunity to run sh.isBalancerRunning() and get a positive.  Interestingly, it took me several tries to get this to return true (just showing two for brevity):

mongos> sh.isBalancerRunning()
false
mongos> sh.isBalancerRunning()
true

Why is that? Well, let's look at the function again:

mongos> sh.isBalancerRunning
function () {
    var x = db.getSisterDB("config").locks.findOne({ _id: "balancer" });
    if (x == null) {
        print("config.locks collection empty or missing. be sure you are connected to a mongos");
        return false;
    }
    return x.state > 0;
}

It is a query on the config database again, this time on the locks collection.  It looks for a lock belonging to the balancer and then returns true if the state is greater than 0.  Here are two examples of the document, one that returns false and one that returns true:

db.getSisterDB("config").locks.findOne({ _id: "balancer" });
{
    "_id" : "balancer",
    "process" : "adamc-mbp:30999:1404425140:16807",
    "state" : 2,
    "ts" : ObjectId("53b5d86fd95df3a66a5975ff"),
    "when" : ISODate("2014-07-03T22:25:51.574Z"),
    "who" : "adamc-mbp:30999:1404425140:16807:Balancer:1622650073",
    "why" : "doing balance round"
}
db.getSisterDB("config").locks.findOne({ _id: "balancer" });
{
    "_id" : "balancer",
    "process" : "adamc-mbp:30999:1404425140:16807",
    "state" : 0,
    "ts" : ObjectId("53b5d86ed95df3a66a5975fe"),
    "when" : ISODate("2014-07-03T22:25:50.528Z"),
    "who" : "adamc-mbp:30999:1404425140:16807:Balancer:1622650073",
    "why" : "doing balance round"
}

If you look closely, you will notice that the ts fields are essentially consecutive, and what you will see with empty chunks is that the non-zero states are very transient.  If I fill up the chunks with data it is far easier to generate a positive.

There you have it - a full explanation of the commands you were running and why you got the results you saw.  I suspect that the root of the question is actually related to a traffic imbalance, but it is not the balancer that generally causes that type of problem (as mentioned before, it is not a load balancer) - traffic imbalance is more likely cause by:

A poor shard key (perhaps very poor)
Balancing is not enabled for the database/collection (see previous pre-split answer for instructions there)
Something is preventing the balancer from working (config server down, balancer window, migration aborts)